Обсуждение: DBI::Oracle problems
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@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@usdata without drama. So could the postgres database the first time!
Can anyone explain what I'm doing wrong, please?
Thanks & Regards
HJR
On Wed, Jun 30, 2010 at 10:10:02AM +1000, Howard Rogers wrote: > 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. Try downloading the latest version of DBI-Link using the "Download Source" link at <http://github.com/davidfetter/DBI-Link> There is also a low-traffic mailing list for the project, where questions like this are more on point :) Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
Thank you David.
I must say, I find mailing lists extremely confusing, and wish there was a "proper" forum type place to go to! My apologies for mailing to the wrong place: I am now not sure whether to keep it here or not! I only wrote here after noting that previous questions about DBI-Link (some going back to 2005, admittedly) were posted here -but, as I say, apologies if that was wrong.
Anyway: I have been using the version 2.0.0 of the DBI-Link, which was last updated three years ago. I see from your link that there are 2010 files available, so I'll definitely give that a whirl.
@Alexander: yes, my server is configured properly. At least, the postgres user and the root user (as well as the oracle user) can all use SQL*Plus to connect to the remote database without having to set any *additional* environment variables, as I mentioned originally. Quite what else I'm supposed to set, if anything, I can't imagine!
I do note, however, that after I run the DBI-Link SQL statements, I get nothing at all in the dbi_link.dbi_connection_environment table, which is probably why it's not working the second time around. Why the function being called can't read the environment variables which are most definitely set, or why it can't insert what it's read into the relevant table, I have no idea.
I'll try with the latest software David linked to and see how I get on.
Thanks to you both,
HJR
On Thu, Jul 1, 2010 at 5:24 AM, David Fetter <david@fetter.org> wrote:
On Wed, Jun 30, 2010 at 10:10:02AM +1000, Howard Rogers wrote:Try downloading the latest version of DBI-Link using the "Download
> 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.
Source" link at <http://github.com/davidfetter/DBI-Link>
There is also a low-traffic mailing list for the project, where
questions like this are more on point :)
Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 01/07/10 07:49, Howard Rogers wrote: > Thank you David. > > I must say, I find mailing lists extremely confusing, and wish there was > a "proper" forum type place to go to! If you prefer web forums, you might want to drop by the EnterpriseDB web forums. In your case that'll be particularly helpful as there are many Oracle users there, and few here. Technically the EDB forums are for the "Postgres Plus" package, but in practice I don't think it matters much. Just make sure to mention what you're using - your PostgreSQL version, where you got it from, etc. See: http://forums.enterprisedb.com It's funny ... when I'm forced to use some web forum, I find myself wishing for a "proper" mailing list to use! Each to their own, I guess. > My apologies for mailing to the > wrong place: I am now not sure whether to keep it here or not! I only > wrote here after noting that previous questions about DBI-Link (some > going back to 2005, admittedly) were posted here -but, as I say, > apologies if that was wrong. It's no big deal. The suggestion was just that there's a more specialized list that people who don't read pgsql-general might read, so you might be more likely to get better help there. -- Craig Ringer Tech-related writing: http://soapyfrogs.blogspot.com/
Just thought I'd write back to say that (I think!) it had nothing to do with the software version, and everything to do with me knowing more about Oracle than PostgreSQL!
I had not been sure how to get PostgreSQL to re-start every time the server bounced, so I followed the advice I'd seen in a number of tutorials dotted around the place (for example, http://www.cyberciti.biz/faq/howto-fedora-linux-install-postgresql-server/ or http://wiki.scinterface.com/index.php/HowTo:_Install_PostgreSQL) , which involved simply issuing the command 'chkconfig on'.
That turns out to have been a mistake: from what I can guess, it means the PostgreSQL service starts before the Oracle environment variables are set.
If instead I simply did 'chkconfig off' and then manually started PostgreSQL (postgres -D /usr/local/pgsql/data >logfile 2>&1 &), then all worked fine, as many times as I liked, and no matter how often I logged off and back on. I dare say some fiddling with the startup script will enable me to do the chkconfig on AND have my environment variables picked up correctly.
Frustrating, nevettheless, but at least it's fixable.
Regards
HJR
On Thu, Jul 1, 2010 at 5:24 AM, David Fetter <david@fetter.org> wrote:
On Wed, Jun 30, 2010 at 10:10:02AM +1000, Howard Rogers wrote:Try downloading the latest version of DBI-Link using the "Download
> 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.
Source" link at <http://github.com/davidfetter/DBI-Link>
There is also a low-traffic mailing list for the project, where
questions like this are more on point :)
Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general