problem connecting oracle and postgres database

[Josh Harrison <joshques@xxxxxxxxx>]

Hi all,

Im trying to create a dbi_link between Oracle and postgresql. i installed all the necessary perl packages
And I had run dbi_link.sql and it completed without any errors

This is sql that I use to connect
postgres@garuda:~$ less /home/postgres/dbi-link-2.0.0/

examples/oracle/dola.sql
/*
 * 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:database=sample;host=111.11.11.11;sid=xxx;port=1521',
    ''username',
    'password',
    '---
AutoCommit: 1
RaiseError: 1
',
    NULL,
    NULL,
    NULL,
    'sample'
);

And it executed successfully .It didn't complain of anything and created rules for all the tables in the oracle database

\d sample.* gives the list of all the tables and their fields

Now when I try to execute this SQL

 select * from sample."ACCESS_METHOD";
I get

NOTICE:  Setting bail in %_SHARED hash. at line 25.

CONTEXT:  SQL statement "SELECT dbi_link.dbi_link_init()"
NOTICE:  Setting quote_literal in %_SHARED hash. at line 25.

CONTEXT:  SQL statement "SELECT dbi_link.dbi_link_init()"
NOTICE:  Setting get_connection_info in %_SHARED hash. at line 25.

CONTEXT:  SQL statement "SELECT dbi_link.dbi_link_init()"
NOTICE:  Setting quote_ident in %_SHARED hash. at line 25.

CONTEXT:  SQL statement "SELECT dbi_link.dbi_link_init()"
NOTICE:  Setting get_dbh in %_SHARED hash. at line 25.

CONTEXT:  SQL statement "SELECT dbi_link.dbi_link_init()"
NOTICE:  Setting remote_exec_dbh in %_SHARED hash. at line 25.

CONTEXT:  SQL statement "SELECT dbi_link.dbi_link_init()"
NOTICE:  SELECT dbi_link.cache_connection( 1 ) at line 12.

NOTICE:  In cache_connection, there's no shared dbh 1 at line 7.

CONTEXT:  SQL statement "SELECT dbi_link.cache_connection( 1 )"

NOTICE:  Entering get_connection_info at line 44.

CONTEXT:  SQL statement "SELECT dbi_link.cache_connection( 1 )"

NOTICE:  ref($args) is HASH
---
data_source_id: 1

CONTEXT:  SQL statement "SELECT dbi_link.cache_connection( 1 )"

NOTICE:  Leaving get_connection_info at line 75.

CONTEXT:  SQL statement "SELECT dbi_link.cache_connection( 1 )"

NOTICE:  ---
auth: passwd
data_source: dbi:Oracle:database=sample;

host=111.11.11.11;sid=xxx;port=1521

dbh_attributes: |
  ---
  AutoCommit: 1
  RaiseError: 1
local_schema: sample
remote_catalog: ~
remote_schema: ~
user_name: sample

CONTEXT:  SQL statement "SELECT dbi_link.cache_connection( 1 )"

NOTICE:  In get_dbh, input connection info is
---

auth: passwd
data_source: dbi:Oracle:database=sample;host=111.11.11.11;sid=xxx;port=1521

dbh_attributes: |
  ---
  AutoCommit: 1
  RaiseError: 1
local_schema: sample
remote_catalog: ~
remote_schema: ~
user_name: sample

CONTEXT:  SQL statement "SELECT dbi_link.cache_connection( 1 )"

ERROR:  error from Perl function "remote_select": error from Perl function "cache_connection": DBI connect('database=sample;host=111.11.11.11;sid=xxx;port=1521','sample',...) failed: ERROR OCIEnvNlsCreate. Check ORACLE_HOME (Linux) env var  or PATH (Windows) and or NLS settings, permissions, etc. at line 137 at line 13.

Can you help? Im able to connect to the Oracle database thro' a perl program but get this error when trying to query thro' dbi_link

Thanks
Josh