Re: DBI-LINK not support special support?

From: paulo matadr <saddoness(at)yahoo(dot)com(dot)br>
To: "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org>
Cc: Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at>
Subject: Re: DBI-LINK not support special support?
Date: 2011-09-14 14:15:21
Message-ID: 1316009721.14343.YahooMailNeo@web114712.mail.gq1.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Environment has been applied
[postgres(at)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 corresponding
                          to your PostgreSQL database encoding
            For example, if your PoatgreSQL database encoding is UTF8, use
            AMERICAN_AMERICA.AL32UTF8
TNS_ADMIN directory containing sqlnet.ora and tnsnames.ora (if you need
            these files)

Other environment variables influence how numbers, dates and timestamps
will be displayed and can be useful if you want to process the data with
PostgreSQL.

You can specify environment variables by passing them in YAML format
as fifth argument to make_accessor_functions(), for example:

---
- env_name: ORACLE_HOME
  env_value: /ORACLE/product/10.2.0
  env_action: overwrite
- env_name: NLS_LANG
  env_value: AMERICAN_AMERICA.AL32UTF8
  env_action: overwrite
- env_name: NLS_DATE_FORMAT
  env_value: "YYYY-MM-DD HH24:MI:SS"
  env_action: overwrite
- env_name: NLS_TIMESTAMP_FORMAT
  env_value: "YYYY-MM-DD HH24:MI:SS.FF"
  env_action: overwrite
- env_name: NLS_TIMESTAMP_TZ_FORMAT
  env_value: "YYYY-MM-DD HH24:MI:SS.FF TZH"
  env_action: overwrite
- env_name: TNS_ADMIN
  env_value: /ORACLE/product/10.2.0/network/admin
  env_action: overwrite
- env_name: LDAP_ADMIN
  env_value: /ORACLE/product/10.2.0/ldap/admin
  env_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(dot)albe(at)wien(dot)gv(dot)at>
Para: paulo matadr <saddoness(at)yahoo(dot)com(dot)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

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message David E. Wheeler 2011-09-14 15:27:50 Re: What Would You Like To Do?
Previous Message Joshua D. Drake 2011-09-14 14:14:10 Re: What Would You Like To Do?