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
postg...@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
