Skip site navigation (1) Skip section navigation (2)

DBI::Oracle problems

From: Howard Rogers <hjr(at)diznix(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: DBI::Oracle problems
Date: 2010-06-30 00:10:02
Message-ID: AANLkTik_KJpjS2eZJtpiiDlquSNj-QaBlB0YHQVCgDX1@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-general
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
[postgres(at)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/password(at)usdata* without drama. So could the postgres database
the first time!

Can anyone explain what I'm doing wrong, please?

Thanks & Regards
HJR

Responses

pgsql-general by date

Next:From: Vick KheraDate: 2010-06-30 01:39:17
Subject: Re: Postgresql partitioning - single hot table or distributed
Previous:From: GeoffreyDate: 2010-06-29 21:38:34
Subject: Re: dropdb weirdness

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group