Beefy Boxes and Bandwidth Generously Provided by pair Networks
Problems? Is your data what you think it is?
 
PerlMonks  

Perl DBI not working with Oracle DBD in 11g r2 environment

by seekhelp (Initiate)
on Nov 22, 2011 at 19:29 UTC ( #939521=perlquestion: print w/ replies, xml ) Need Help??
seekhelp has asked for the wisdom of the Perl Monks concerning the following question:

version :
oracle database: 11g r2
Perl DBI version 1.21
ORACLE DBD version 1.06

ERROR :DBI->connect() failed: (UNKNOWN OCI STATUS 1804) OCIInitialize. Check ORACLE_HOME and NLS settings etc. at /u00/app/xxx/site/perl-5.6.1/lib/site_perl/5.6.1/refdb.pm

CODE : Set Oracle date format BEGIN { $ENV{NLS_LANG}="AMERICAN_AMERICA.WE8MSWIN1252"; } use common_lib_t11; #${&dbh}->do("alter session set NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:S +S'"); print 'Got date: ' . ${&dbh}->selectrow_array("SELECT SYSDATE FROM DUA +L") . "\n"; print 'Got global_name: ' . ${&dbh}->selectrow_array('select * from gl +obal_name') . "\n";
----------------- use common_lib_t11 -----------------
package main; BEGIN { my @passwd = getpwnam "xxxxr"; $HOME_DIR = $passwd[7]; $ENV{"PATH"} = $HOME_DIR."/bin:".$ENV{"PATH"}; foreach my $dir ("/opt/bin","/usr/local/bin") { if (-d $dir) {$ENV{"PATH"}.=":$dir"} } $ENV{"ORACLE_SID"} = "xxxxx"; } # Oracle login $DB_USERID = "xxxx"; $DB_PASSWD = "xxxx"; $DB_SID = "xxxxx"; # file paths $BASE_DIR = "$HOME_DIR/XX"; # misc if ($ENV{"LD_LIBRARY_PATH"}) { $ENV{"LD_LIBRARY_PATH"} .= ":$CHOME_DIR/lib:/usr/local/lib:/usr/li +b:/usr/ucblib:/usr/openwin/lib:/usr/dt/lib"; } else { $ENV{"LD_LIBRARY_PATH"} = "$HOME_DIR/lib:/usr/local/lib:/usr/lib:/ +usr/ucblib:/usr/openwin/lib:/usr/dt/lib";} package common_lib_t11; use Carp; use MIME::Entity; ($VERSION = '$RCSfile: common_lib.pm,v $$Revision: 1.22 $$Date: 2006/0 +3/01 17:01:48 $ ') =~ s/RCSfile|Revision|Date|\$|: //g; ------------------------------------
General detail :
This process is working fine with 9i but the moment we try to run it against 11g r2 is give error. This box has two homes with 9i and other 11g. I am thinking that could be an issue.

Below already tried :

Try to sent the Time zone varirable. ORA_TZFILE
Check the compatibility of versions.
Try to set oracle_home and LD_Library, oracle_sid in the scripts also.

Comment on Perl DBI not working with Oracle DBD in 11g r2 environment
Select or Download Code
Re: Perl DBI not working with Oracle DBD in 11g r2 environment
by Tux (Monsignor) on Nov 22, 2011 at 20:15 UTC

    So you jump from oracle 9 to Oracle 11, which probably took over 7 hours to complete, but you are still at perl-5.6.1 (released on 2001-04-08), DBI-1.21 (released 2002-02-07) and DBD::Oracle-1.06 (released 2000-07-14).

    Current is perl-5.14.2, DBI-1.616, and DBD::Oracle-1.34. Maybe, just maybe, these new versions might have "fixed" issues with these newer Oracle releases.

    I do not know exactly when exactly Oracle-11 was released, but I'm sure it was waaaaaaay later than 14-07-2000.

    I am very very sure noone will fix this bug for you with these old versions.

    update: Here is the Oracle release scheme (published 2008-11-11):

    Release Released Oracle 6 1988 Oracle 7 1992 Oracle 8 1997 Oracle 8i 1998 Oracle 9i 2001 Oracle 10g 2004 Oracle 11g 2007

    There is no way bugs in DBD::Oracle (or DBI) related to Oracle released later than the versions you use could have been fixed/tested before this newer version was available to test with.


    Enjoy, Have FUN! H.Merijn
      Thank you very much for you response !!
      I will try to get PERL DBI upgraded.
      I think there is some libraries from 9i that are getting used my 11g.

      ------------------------------- Even below code is failing:
      </code>
      #!/u00/app/xxx/bin/perl
      my $ORACLE_HOME = "/u00/app/oracle/product/11.2.0.2";
      my $ORACLE_SID="xxxxT11";
      $ENV{ORACLE_HOME}=$ORACLE_HOME;
      $ENV{ORACLE_SID}=$ORACLE_SID;
      $ENV{PATH}="$ORACLE_HOME/bin";
      $ENV{LD_LIBRARY_PATH}="$ORACLE_HOME/lib";
      use strict;
      use DBI;
      my $dbh = DBI->connect( 'dbi:Oracle:orcl', 'scott', 'tiger', ) || die "Database connection not made: $DBI::errstr";
      $dbh->disconnect;
      </code>
      ----------------------- Error : failed: (UNKNOWN OCI STATUS 1804) OCIInitialize. Check ORACLE_HOME and NLS settings etc. at newtest line 10
      -------------------------

      its not even checking my Credentials..( scott is not login user ) failing to connect.
      ----------------------- Please let me know if I am missing something here !!


      Thanks all !!


        Just FYI, it is way more important to update DBD::Oracle than it is to update DBI. The latter has no knowledge of oracle connections at all. Current DBD::Oracle requires DBI version 1.51 (since 27-01-2011, DBD::Oracle-1.28). Before that, it had no prerequired version for DBI. You just had to keep your hopes up.


        Enjoy, Have FUN! H.Merijn

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://939521]
Approved by marto
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others making s'mores by the fire in the courtyard of the Monastery: (5)
As of 2014-11-27 20:57 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    My preferred Perl binaries come from:














    Results (188 votes), past polls