spexe has asked for the wisdom of the Perl Monks concerning the following question:

Hi,

I am getting following error while connecting to oracle 11g database.

ERROR: 1804 '(UNKNOWN OCI STATUS 1804) OCIInitialize. Check ORACLE_HOME and NLS settings etc.

I am able to access the Oracle11g database from oracle 10g client through remote machine.

Please guide me to resolving this issue.

Points: -------- - perl,DBI & DBD versions: perl : 5.6.1 DBI : 1.21 DBD : 1.12

- perl program 

#!/usr/bin/perl -w
use strict ;
use DBI ;

DBI->trace(15);
my $dbh = DBI->connect('dbi:Oracle:confdb', 'admin', 'admin',{'RaiseEr
+ror'=>1}) || die "Database connection not made: $DBI::errstr";

my $sth = $dbh->prepare("select obj_name from objecttype") ; $sth->exe
+cute ;
my $row = $sth->fetchrow_arrayref() ;
print ">>> $row->[0] <<<\n" ;
$sth->finish ;
$dbh->disconnect ;

[download]

- perl program trace file. 

oracle@cmbsr-> a.pl
    DBI 1.21-nothread dispatch trace level set to 15
    -> DBI->connect(dbi:Oracle:confdb, admin, ****, HASH(0x1002279e0))
    -> DBI->install_driver(Oracle) for solaris perl=5.006001 pid=24526
+ ruid=100 euid=100
       install_driver: DBD::Oracle version 1.12 loaded from /usr/lib/p
+erl5/site_perl/5.6.1/sun4-solaris/DBD/Oracle.pm
    New DBI::dr (for DBD::Oracle::dr, parent=, id=)
    dbih_setup_handle(DBI::dr=HASH(0x10034c170)=>DBI::dr=HASH(0x1003dc
+718), DBD::Oracle::dr, 0, Null!)
    dbih_make_com(Null!, DBD::Oracle::dr, 160)
    dbih_setup_attrib(DBI::dr=HASH(0x1003dc718), Err, Null!) SCALAR(0x
+100306990) (already defined)
    dbih_setup_attrib(DBI::dr=HASH(0x1003dc718), State, Null!) SCALAR(
+0x100350428) (already defined)
    dbih_setup_attrib(DBI::dr=HASH(0x1003dc718), Errstr, Null!) SCALAR
+(0x100306950) (already defined)
    dbih_setup_attrib(DBI::dr=HASH(0x1003dc718), Handlers, Null!) ARRA
+Y(0x100306d38) (already defined)
    dbih_setup_attrib(DBI::dr=HASH(0x1003dc718), Debug, Null!) 0 (alre
+ady defined)
    dbih_setup_attrib(DBI::dr=HASH(0x1003dc718), FetchHashKeyName, Nul
+l!) 'NAME' (already defined)
    <- install_driver= DBI::dr=HASH(0x10034c170)
    >> connect     DISPATCH (DBI::dr=HASH(0x10034c170) rc1/3 @5 g0 a10
+03a9280) at /usr/lib/perl5/site_perl/5.6.1/sun4-solaris/DBI.pm line 4
+71 via ./a.pl line 6
    -> connect for DBD::Oracle::dr (DBI::dr=HASH(0x10034c170)~0x1003dc
+718 'confdb' 'admin' **** HASH(0x1003127b8))
    New DBI::db (for DBD::Oracle::db, parent=DBI::dr=HASH(0x1003dc718)
+, id=)
    dbih_setup_handle(DBI::db=HASH(0x1003d94a8)=>DBI::db=HASH(0x100306
+c28), DBD::Oracle::db, 1003475b8, Null!)
    dbih_make_com(DBI::dr=HASH(0x1003dc718), DBD::Oracle::db, 208)
    dbih_setup_attrib(DBI::db=HASH(0x100306c28), Err, DBI::dr=HASH(0x1
+003dc718)) SCALAR(0x100306990) (already defined)
    dbih_setup_attrib(DBI::db=HASH(0x100306c28), State, DBI::dr=HASH(0
+x1003dc718)) SCALAR(0x100350428) (already defined)
    dbih_setup_attrib(DBI::db=HASH(0x100306c28), Errstr, DBI::dr=HASH(
+0x1003dc718)) SCALAR(0x100306950) (already defined)
    dbih_setup_attrib(DBI::db=HASH(0x100306c28), Handlers, DBI::dr=HAS
+H(0x1003dc718)) ARRAY(0x100306d38) (already defined)
    dbih_setup_attrib(DBI::db=HASH(0x100306c28), Debug, DBI::dr=HASH(0
+x1003dc718)) 0 (already defined)
    dbih_setup_attrib(DBI::db=HASH(0x100306c28), FetchHashKeyName, DBI
+::dr=HASH(0x1003dc718)) 'NAME' (already defined)
    dbih_setup_attrib(DBI::db=HASH(0x100306c28), HandleError, DBI::dr=
+HASH(0x1003dc718)) undef (not defined)
Environment variables:
        MANPATH=:/usr/share/man
        TERM=vt100
        SHELL=/bin/ksh
        SSH_CLIENT=153.58.26.145 2522 22
        SSH_TTY=/dev/pts/1
        USER=oracle
        LD_LIBRARY_PATH=/ora00/app/oracle/product/11.2.0/db_1/lib
        ORACLE_SID=confdb
        ORACLE_BASE=/ora00/app/oracle
        MAIL=/var/mail//oracle
        PATH=/usr/bin:/ora00/app/oracle/product/11.2.0/db_1/bin:.
        PWD=/ora00/app/oracle
        EDITOR=vi
        TZ=Canada/Eastern
        PS1=oracle@cmbsr->
        SHLVL=1
        HOME=/ora00/app/oracle
        NONABI_SCRIPTS=true
        LOGNAME=oracle
        SSH_CONNECTION=153.58.26.145 2522 153.71.78.219 22
        ORACLE_HOME=/ora00/app/oracle/product/11.2.0/db_1
        _=./a.pl
OCIInitialize(2,0,0,0,0)=(UNKNOWN OCI STATUS 1804)
    ERROR EVENT 1804 '(UNKNOWN OCI STATUS 1804) OCIInitialize. Check O
+RACLE_HOME and NLS settings etc.' on DBI::db=HASH(0x1003d94a8)
    !! ERROR: 1804 '(UNKNOWN OCI STATUS 1804) OCIInitialize. Check ORA
+CLE_HOME and NLS settings etc.'
    <- connect= undef at /usr/lib/perl5/site_perl/5.6.1/sun4-solaris/D
+BI.pm line 471 via ./a.pl line 6
    >> errstr      DISPATCH (DBI::dr=HASH(0x10034c170) rc1/4 @1 g0 a10
+03a9200) at /usr/lib/perl5/site_perl/5.6.1/sun4-solaris/DBI.pm line 4
+72 via ./a.pl line 6
    -> errstr in DBD::_::common for DBD::Oracle::dr (DBI::dr=HASH(0x10
+034c170)~0x1003dc718)
    <- errstr= '(UNKNOWN OCI STATUS 1804) OCIInitialize. Check ORACLE_
+HOME and NLS settings etc.' at /usr/lib/perl5/site_perl/5.6.1/sun4-so
+laris/DBI.pm line 472 via ./a.pl line 6
DBI->connect(confdb) failed: (UNKNOWN OCI STATUS 1804) OCIInitialize. 
+Check ORACLE_HOME and NLS settings etc. at ./a.pl line 6
    >> DESTROY     DISPATCH (DBI::db=HASH(0x1003d94a8) rc1/1 @1 g0 a0)
    <> DESTROY ignored for outer handle DBI::db=HASH(0x1003d94a8) (inn
+er DBI::db=HASH(0x100306c28))
    >> DESTROY     DISPATCH (DBI::db=HASH(0x100306c28) rc1/1 @1 g0 a0)
    -> DESTROY for DBD::Oracle::db (DBI::db=HASH(0x100306c28)~INNER)
         DESTROY for DBI::db=HASH(0x100306c28) ignored - handle not in
+itialised
    <- DESTROY= undef
    dbih_clearcom (h 0x1003d94a8, com 0x1003d6938):
       FLAGS 0x11: COMSET Warn
       TYPE 2
       PARENT DBI::dr=HASH(0x1003dc718)
       KIDS 0 (0 active)
       IMP_DATA undef in 'DBD::Oracle::db'
    dbih_clearcom 0x1003d94a8 (com 0x1003d6938, type 2) done.

    -- DBI::END
    >> disconnect_all DISPATCH (DBI::dr=HASH(0x10034c170) rc1/3 @1 g0 
+a1003a91a0) at /usr/lib/perl5/site_perl/5.6.1/sun4-solaris/DBI.pm lin
+e 533 via ./a.pl line 0
    -> disconnect_all for DBD::Oracle::dr (DBI::dr=HASH(0x10034c170)~0
+x1003dc718)
    <- disconnect_all= '' at /usr/lib/perl5/site_perl/5.6.1/sun4-solar
+is/DBI.pm line 533 via ./a.pl line 0
    >> DESTROY     DISPATCH (DBI::dr=HASH(0x1003dc718) rc1/1 @1 g0 a0)
+ during global destruction
    -> DESTROY in DBD::_::common for DBD::Oracle::dr (DBI::dr=HASH(0x1
+003dc718)~INNER)
    <- DESTROY= undef during global destruction
    dbih_clearcom (h 0x10034c170, com 0x1003d6840):
       FLAGS 0x215: COMSET Active Warn AutoCommit
       TYPE 1
       PARENT undef
       KIDS 0 (0 active)
       IMP_DATA undef in 'DBD::Oracle::dr'
    dbih_clearcom 0x10034c170 (com 0x1003d6840, type 1) done.

    >> DESTROY     DISPATCH (DBI::dr=HASH(0x10034c170) rc1/1 @1 g0 a0)
+ during global destruction
    <> DESTROY for DBI::dr=HASH(0x10034c170) ignored (inner handle gon
+e)

[download]

Thanks & Regards, Saurabh

Replies are listed 'Best First'.
Re: ERROR: 1804 '(UNKNOWN OCI STATUS 1804) OCIInitialize
by moritz (Cardinal) on May 12, 2010 at 13:33 UTC
    versions: perl : 5.6.1 DBI : 1.21 DBD : 1.12

    Without knowing much about oracle, my first approach would be to try non-ancient versions of everything (perl 5.6.1. is more than 9 years old, DBD::Oracle 1.12 is so old that the search.cpan.org interface doesn't show it anymore, DBI 1.21 is also at least 4 years old).

    Many problems have been fixed since the Auld Days, and yours might (mind you, might, not "is") be one of them.

    Perl 6 - links to (nearly) everything that is Perl 6.
[reply]
[d/l]

      I recently came across this problem when using various Oracle installs and the Oracle::DBD that was located there.

      I had two installs - an Oracle 10g & and Oracle 11g. The perl libraries I was using were the ones installed with Oracle 11g.

      When the perl program ran without altering the ORACLE_HOME env variable ( $ENV{ORACLE_HOME} ) , this worked fine.

      However, if the $ENV{ORACLE_HOME} was set within the perl program to use the Oracle 10g, then I got this error message.

      A simple trace on the database showed that this perl client wasn't connecting, so the problem had to be in the client, not the database.

      Now error message ORA-01804 suggests that the client couldn't find the timezone information - it normally expects to find it in $ORACLE_HOME/oracore/zoneinfo/timezlrg.dat.

      For my Oracle 11g install, this file wasn't present.

      However, the location of this file can be altered using the environment variable ORA_TZFILE ( Oracle support note ID 365558.1 discusses this), so by setting this environment variable, I could get my test programs to work correctly.

      Hope this helps.

[reply]
        $ORACLE_HOME/oracore/zoneinfo/timezlrg.dat...

        I haven't worked much with oracle but I didn't think it was this ridiculous

[reply]

Back to Seekers of Perl Wisdom