Thread: DBI-LINK not support special support?
Hi all,
I use dbi-link to connect for oracle db 10g and 11g, and
big problem give to me:
example:
select * from table(oracle)
çavân
When dbi-link call information from oracle his show
?cv?an
çavân
When dbi-link call information from oracle his show
?cv?an
In pure perl script no have problems too.
Any ideas for help me?
Regards,
Paulo
paulo matadr wrote: > I use dbi-link to connect for oracle db 10g and 11g, and > big problem give to me: > example: > select * from table(oracle) > çavân > When dbi-link call information from oracle his show > ?cv?an > > In pure perl script no have problems too. > > Any ideas for help me? This is not a question for the hackers list. Set the environment variable NLS_LANG for the postgres process to the correct Oracle character set. Yours, Laurenz Albe
Environment has been applied
[postgres@gcomdesenv oracle]$ export
declare -x HOME="/usr/local/pgsql/data"
declare -x INPUTRC="/etc/inputrc"
declare -x JAVA_HOME="/usr/java/jdk1.6.0_26"
declare -x LANG="en_US.UTF-8"
declare -x LANGUAGE="en_US.UTF-8"
declare -x LC_ALL="en_US"
declare -x LC_CTYPE="UTF8"
declare -x LD_LIBRARY_PATH="/opt/va/linux:/usr/lib/oracle/11.2/client64/lib"
declare -x NLS_LANG="AMERICAN_AMERICA.AL32UTF8"
declare -x NLS_NCHAR="AL32UTF8"
declare -x OLDPWD="/usr/local/pgsql/dbi-link-2.0.0"
declare -x ORACLE_HOME="/usr/lib/oracle/11.2/client64"
declare -x PATH="/usr/java/jdk1.6.0_26/bin:/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/pgsql/bin:/usr/bin:/bin:/usr/local/bin:/usr/lib/oracle/11.2/client64/bin"
declare -x PGDATA="/usr/local/pgsql/data"
declare -x PGLOCAL="/usr/local/pgsql/data"
declare -x PWD="/usr/local/pgsql/dbi-link-2.0.0/examples/oracle"
declare -x TNS_ADMIN="/usr/lib/oracle/11.2/client64"
But reading official page on https://github.com/davidfetter/DBI-Link/blob/master/README.Oracle I see this:
/*
* Data source: dbi:Oracle:hr;host=localhost;sid=xe
* User: hr
* Password: foobar
* dbh attributes: {AutoCommit => 1, RaiseError => 1}
* dbh environment: NULL
* remote schema: NULL
* remote catalog: NULL
* local schema: hr
*/
UPDATE
pg_catalog.pg_settings
SET
setting =
CASE WHEN 'dbi_link' = ANY(string_to_array(setting, ','))
THEN setting
ELSE 'dbi_link,' || setting
END
WHERE
name = 'search_path'
;
SELECT make_accessor_functions(
'dbi:Oracle:hr;host=localhost;sid=xe',
'hr',
'foobar',
'---
AutoCommit: 1
RaiseError: 1
',
NULL, --dbh environment:
NULL,
NULL,
'hr'
);
-------------------------------------------------------------------
In function make_accessor_functions() , the manual tell to put in dbh environment env variables but I not sucess .
I try this:
SELECT make_accessor_functions(
'dbi:Oracle:dbi:Oracle:hr;host=localhost;sid=xe',
'hr',
'foobar',
'---
AutoCommit: 1
RaiseError: 1
',
'
-env_name: NLS_LANG
env_value: AMERICAN_AMERICA.AL32UTF8
env_action: overwrite
',
NULL,
NULL,
'hr'
);
ERROR: error from Perl function "make_accessor_functions": error from Perl function "set_up_connection": error from Perl function "add_dbi_connection_environment": In dbi_link.add_dbi_connection_environment, settings is a >HASH<, not an array reference at line 8. at line 94. at line 35.
Any ideia for help
Regards
[postgres@gcomdesenv oracle]$ export
declare -x HOME="/usr/local/pgsql/data"
declare -x INPUTRC="/etc/inputrc"
declare -x JAVA_HOME="/usr/java/jdk1.6.0_26"
declare -x LANG="en_US.UTF-8"
declare -x LANGUAGE="en_US.UTF-8"
declare -x LC_ALL="en_US"
declare -x LC_CTYPE="UTF8"
declare -x LD_LIBRARY_PATH="/opt/va/linux:/usr/lib/oracle/11.2/client64/lib"
declare -x NLS_LANG="AMERICAN_AMERICA.AL32UTF8"
declare -x NLS_NCHAR="AL32UTF8"
declare -x OLDPWD="/usr/local/pgsql/dbi-link-2.0.0"
declare -x ORACLE_HOME="/usr/lib/oracle/11.2/client64"
declare -x PATH="/usr/java/jdk1.6.0_26/bin:/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/pgsql/bin:/usr/bin:/bin:/usr/local/bin:/usr/lib/oracle/11.2/client64/bin"
declare -x PGDATA="/usr/local/pgsql/data"
declare -x PGLOCAL="/usr/local/pgsql/data"
declare -x PWD="/usr/local/pgsql/dbi-link-2.0.0/examples/oracle"
declare -x TNS_ADMIN="/usr/lib/oracle/11.2/client64"
But reading official page on https://github.com/davidfetter/DBI-Link/blob/master/README.Oracle I see this:
--------------------------------------------Environment variables:----------------------Oracle needs certain environment variables to function correctly:ORACLE_HOME must be set to the directory where the software is installed(not required with Instant Client)NLS_LANG must be of the form <territory>_<language>:<charset>, where<territory> influences settings like the decimal separator(use AMERICAN if you want a period)<language> is the language for Oracle error messages<charset> must be the Oracle character set correspondingto your PostgreSQL database encodingFor example, if your PoatgreSQL database encoding is UTF8, useAMERICAN_AMERICA.AL32UTF8TNS_ADMIN directory containing sqlnet.ora and tnsnames.ora (if you needthese files)Other environment variables influence how numbers, dates and timestampswill be displayed and can be useful if you want to process the data withPostgreSQL.You can specify environment variables by passing them in YAML formatas fifth argument to make_accessor_functions(), for example:---- env_name: ORACLE_HOMEenv_value: /ORACLE/product/10.2.0env_action: overwrite- env_name: NLS_LANGenv_value: AMERICAN_AMERICA.AL32UTF8env_action: overwrite- env_name: NLS_DATE_FORMATenv_value: "YYYY-MM-DD HH24:MI:SS"env_action: overwrite- env_name: NLS_TIMESTAMP_FORMATenv_value: "YYYY-MM-DD HH24:MI:SS.FF"env_action: overwrite- env_name: NLS_TIMESTAMP_TZ_FORMATenv_value: "YYYY-MM-DD HH24:MI:SS.FF TZH"env_action: overwrite- env_name: TNS_ADMINenv_value: /ORACLE/product/10.2.0/network/adminenv_action: overwrite- env_name: LDAP_ADMINenv_value: /ORACLE/product/10.2.0/ldap/adminenv_action: overwrite
/*
* Data source: dbi:Oracle:hr;host=localhost;sid=xe
* User: hr
* Password: foobar
* dbh attributes: {AutoCommit => 1, RaiseError => 1}
* dbh environment: NULL
* remote schema: NULL
* remote catalog: NULL
* local schema: hr
*/
UPDATE
pg_catalog.pg_settings
SET
setting =
CASE WHEN 'dbi_link' = ANY(string_to_array(setting, ','))
THEN setting
ELSE 'dbi_link,' || setting
END
WHERE
name = 'search_path'
;
SELECT make_accessor_functions(
'dbi:Oracle:hr;host=localhost;sid=xe',
'hr',
'foobar',
'---
AutoCommit: 1
RaiseError: 1
',
NULL, --dbh environment:
NULL,
NULL,
'hr'
);
-------------------------------------------------------------------
In function make_accessor_functions() , the manual tell to put in dbh environment env variables but I not sucess .
I try this:
SELECT make_accessor_functions(
'dbi:Oracle:dbi:Oracle:hr;host=localhost;sid=xe',
'hr',
'foobar',
'---
AutoCommit: 1
RaiseError: 1
',
'
-env_name: NLS_LANG
env_value: AMERICAN_AMERICA.AL32UTF8
env_action: overwrite
',
NULL,
NULL,
'hr'
);
ERROR: error from Perl function "make_accessor_functions": error from Perl function "set_up_connection": error from Perl function "add_dbi_connection_environment": In dbi_link.add_dbi_connection_environment, settings is a >HASH<, not an array reference at line 8. at line 94. at line 35.
Any ideia for help
Regards
Paulo
De: Albe Laurenz <laurenz.albe@wien.gv.at>
Para: paulo matadr <saddoness@yahoo.com.br>;
Enviadas: Quarta-feira, 14 de Setembro de 2011 4:05
Assunto: Re: [HACKERS] DBI-LINK not support special support?
paulo matadr wrote:
> I use dbi-link to connect for oracle db 10g and 11g, and
> big problem give to me:
> example:
> select * from table(oracle)
> çavân
> When dbi-link call information from oracle his show
> ?cv?an
>
> In pure perl script no have problems too.
>
> Any ideas for help me?
This is not a question for the hackers list.
Set the environment variable NLS_LANG for the postgres process
to the correct Oracle character set.
Yours,
Laurenz Albe
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Wed, Sep 14, 2011 at 9:15 AM, paulo matadr <saddoness@yahoo.com.br> wrote: > Any ideia for help You are much more likely to get help if you ask on the right mailing list, instead of here. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
paulo matadr wrote: [has problems displaying non-ASCII characters] > Environment has been applied > [postgres@gcomdesenv oracle]$ export [...] > declare -x NLS_LANG="AMERICAN_AMERICA.AL32UTF8" The environment has not been applied correctly, because you see question marks, which is Oracle's replacement for characters it cannot translate. I don't know what went wrong. The environment variable must be set when the postmaster is started. [...] > You can specify environment variables by passing them in YAML format > as fifth argument to make_accessor_functions(), for example: [...] > ERROR: error from Perl function "make_accessor_functions": error from Perl function > "set_up_connection": error from Perl function "add_dbi_connection_environment": In > dbi_link.add_dbi_connection_environment, settings is a >HASH<, not an array reference at line 8. at > line 94. at line 35. Your YAML is wrong. > Any ideia for help As Robert Haas mentioned, write to dbi-link-general: http://lists.pgfoundry.org/mailman/listinfo/dbi-link-general Yours, Laurenz Albe