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";

[download]
----------------- 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;
------------------------------------

[download]
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.

Replies are listed 'Best First'.
Re: Perl DBI not working with Oracle DBD in 11g r2 environment
by Tux (Canon) 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

    [download]

    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
[reply]
[d/l]
      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 !!


[reply]

        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
[reply]

Back to Seekers of Perl Wisdom