I am stumped, despite working on this for a week! I am trying to create a 64-bit postgresql 8.4 database server which can retrieve data from various 64-bit Oracle 10gR2 and 11gR2 databases.
- I have a freshly-installed 64-bit Centos 5.5, no firewall, no SELinux. - I create an oracle user and do a run-time 11gR2 Client installation (so, the full-blown client, not the instant client) - I set ORACLE_HOME, ORACLE_BASE, PATH, LD_LIBRARY_PATH, CLASSPATH, ORA_NLS10, TWO_TASK in /etc/profile (see below for precise details) - I can connect to my Oracle database (on a remote server) in SQL*Plus, both as the root user and as the oracle user. - I then create a new postgres user, install postgresql, create a new superuser, create a new database owned by the new superuser and confirm the new user can connect to the new database. - As root, I used cpan to install DVI, DBD::Oracle and YAML. - As root, I did *yum install postgresql-plperl* - As the new postgres superuser, I did *create language plperlu* - I downloaded the dbi-link software from http://pgfoundry.org/projects/dbi-link. - Still as the new postgres superuser, I ran the *dbi_link.sql* script contained in that download - Then I ran the two SQL statements contained in the README found in that download. The second of these causes a bunch of _shadow tables and views to be created as a select from a schema in one of the remote Oracle databases, which is the good news part. If I then immediate select from one of those tables, I get data returned, which is really excellent news ...but the good news ends shortly after that, as this demonstrates: ims=# select "BRAND_ID" from usdata."BRAND_S"; NOTICE: SELECT dbi_link.cache_connection( 1 ) at line 12. BRAND_ID ---------- 1032 1115 1254 ... 2454 2455 2114 2474 2475 (290 rows) ims=# \q [postg...@pgx64 ~]$ psql -d ims psql (8.4.4) Type "help" for help. ims=# select "BRAND_ID" from usdata."BRAND_S"; 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: password data_source: dbi:Oracle:database=usdata;sid=usdata;host=192.168.0.60 dbh_attributes: | --- AutoCommit: 1 RaiseError: 1 local_schema: usdata remote_catalog: ~ remote_schema: ~ user_name: remoteuser CONTEXT: SQL statement "SELECT dbi_link.cache_connection( 1 )" NOTICE: In get_dbh, input connection info is --- auth: password data_source: dbi:Oracle:database=usdata;sid=usdata;host=192.168.0.60 dbh_attributes: | --- AutoCommit: 1 RaiseError: 1 local_schema: usdata remote_catalog: ~ remote_schema: ~ user_name: remoteuser 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=usdata;sid=usdata;host=192.168.0.60','remoteuser',...) failed: ERROR OCIEnvNlsCreate. Check ORACLE_HOME (Linux) env var or PATH (Windows) and or NLS settings, permissions, etc. at line 137 at line 13. ims=# In my many, many tests, I have read a lot of posts on Google and elsewhere about the need to set ORACLE_HOME, LD_LIBRARY_PATH and so on to avoid these problems... but what I don't get is that the only difference between my two selects is that I quit out of psql! If the environment variables were wrong second time round, why were they OK the first time?! For the record, here's the contents of my /etc/profile: ORACLE_BASE=/u01/app/oracle ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 ORACLE_SID=usdata export ORACLE_BASE ORACLE_HOME ORACLE_SID export ORA_NLS10=/u01/app/oracle/product/11.2.0/db_1/nls/data export TWO_TASK=usdata export ORA_USERID=remoteuser/password export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1 PATH=$ORACLE_HOME/bin:$PATH:. export PATH LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib64:/lib:/usr/lib64:/usr/lib CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib export LD_LIBRARY_PATH CLASSPATH export DISTRIB_RELEASE=5 Root, the oracle user and the postgres user can all do *sqlplus remoteuser/passw...@usdata* without drama. So could the postgres database the first time! Can anyone explain what I'm doing wrong, please? Thanks & Regards HJR