I does seem to be an ORACLE environment issue.
IIRC, the original post had the Oracle environment variables set in a
BEGIN block.
I ran into a similar issue, where I changed ORACLE_HOME and TNS_ADMIN to
point to a new location. I set these in a BEGIN block in my startup.pl
file. But the mod_perl code kept going back to the old location.
I finally tracked it down that another section of my code base was setting
the Oracle environment variables back to their original locations. Once
I tracked that down and corrected it, I had no more problems. But for
awhile I thought either mod_perl was loosing it.
So...."it could be" that another script (ie. something loaded in the
startup.pl or equivalent) is affecting the Oracle environment variables
in some way. That would be consistent with your observations that the
script works fine on the command-line, but not in the mod_perl environment.
You might want to print out the Oracle environment variables right before
you do your connect, just to test this.
Hopefully it's that simple.
Quoting Atsushi Fujita ([EMAIL PROTECTED]):
> Hi Fredo,
>
> I had a similar problem in similar environment.
> My simple DBD program worked fine on normal shell and normal apache, but it
> didn't work under the mod_perl2.
> It showed error as following in apache error_log.
>
> ----[error_log]----
> DBI->connect(ynt0) failed: (UNKNOWN OCI STATUS 1804) OCIInitialize. Check
> ORACLE_HOME and NLS settings etc. at
> /yopt/httpd-2.0.39_prefork_perl5.6.1normal/cgi-bin/test1.cgi line 42
> [Mon Jul 29 20:15:37 2002] [error] 25703: ModPerl::Registry: `Cannot connect:
> (UNKNOWN OCI STATUS 1804) OCIInitialize. Check ORACLE_HOME and
> NLS settings etc.at
> /yopt/httpd-2.0.39_prefork_perl5.6.1normal/cgi-bin/test1.cgi line 42.
> --------
>
> This looks like a problem of %ENV.
> So, I inserted debug code in DBD.pm to output %ENV, and it was correct in
> DBD.pm under mod_perl2.
> But I didn't check deep C code, I gave up to check it...
>
> My machine is as following.
> - perl5.6.1(non thread)
> - DBI-1.30
> - DBD-Oracle-1.12
> - mod_perl1.99_04(DSO build)
> - apache2.0.39(prefork)
> - Oracle9.0.1.3 for Linux in same machine
>
> And I attached my last mail.
> Sorry for being long mail...
>
> Thank you.
>
> Atsushi
>
>
> ----- Original Message -----
> From: "Atsushi Fujita" <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>
> Sent: Monday, July 29, 2002 8:39 PM
> Subject: mod_perl2 & DBD::Oracle problem
>
>
> > Hi all,
> >
> > I am trying to use DBD::Oracle1.12 on mod_perl2.
> > But it doesn't work fine.
> > It shows error as following in error_log at $dbh = DBI->connect.
> >
> > ----[error_log]----
> > DBI->connect(ynt0) failed: (UNKNOWN OCI STATUS 1804) OCIInitialize. Check
> > ORACLE_HOME and NLS settings etc. at
> > /yopt/httpd-2.0.39_prefork_perl5.6.1normal/cgi-bin/test1.cgi line 42
> > [Mon Jul 29 20:15:37 2002] [error] 25703: ModPerl::Registry: `Cannot
> connect:
> > (UNKNOWN OCI STATUS 1804) OCIInitialize. Check ORACLE_HOME and
> > NLS settings etc.at
> > /yopt/httpd-2.0.39_prefork_perl5.6.1normal/cgi-bin/test1.cgi line 42.
> > --------
> >
> >
> > ----[test1.cgi]----
> > use DBI;
> >
> > my $dsn = 'dbi:Oracle:';
> > my $user = 'username/password';
> > my $password = '';
> >
> > $ENV{'ORACLE_HOME'} = '/u01/app/oracle/product/9.0.1';
> > $ENV{'ORACLE_SID'} = 'ynt0';
> > $ENV{'NLS_LANG'} = 'japanese_japan.ja16euc';
> >
> > print "ORACLE_HOME=$ENV{'ORACLE_HOME'}<br>\n";
> > print "ORACLE_SID=$ENV{'ORACLE_SID'}<br>\n";
> > print "NLS_LANG=$ENV{'NLS_LANG'}<br>\n";
> > print "DSN=$dsn$ENV{'ORACLE_SID'}<br>\n";
> >
> > $dbh = DBI->connect("$dsn$ENV{'ORACLE_SID'}", $user, $password)
> > or die "Cannot connect: ".$DBI::errstr;
> > ...
> > --------
> >
> > At first, I suspect that the reason is %ENV in this script.
> > But I set surely, and this output as following.
> >
> > ----[Broser output HTML]----
> > ORACLE_HOME=/u01/app/oracle/product/9.0.1
> > ORACLE_SID=ynt0
> > NLS_LANG=japanese_japan.ja16euc
> > DSN=dbi:Oracle:ynt0
> >
> > Internal Server Error
> > The server encountered an internal error or misconfiguration and was unable
> to
> > complete your request.
> > ....
> > --------
> >
> > And if I turned off the mod_perl, it works fine on normal CGI-script.
> > This error occurred only mod_perl.
> > (I tested mod_perl1.26 & apache 1.3.26, it worked fine...)
> >
> > My machine is as following.
> > - perl5.6.1(non thread)
> > - DBI-1.30
> > - DBD-Oracle-1.12
> > - mod_perl1.99_04(DSO build)
> > - apache2.0.39(prefork)
> >
> >
> > ----[httpd.conf]----(is this wrong??)
> > LoadModule perl_module modules/mod_perl.so
> > .......
> > <IfModule mod_perl.c>
> > PerlModule ModPerl::Registry
> > <Location /cgi-bin>
> > SetHandler perl-script
> > PerlResponseHandler ModPerl::Registry
> > PerlOptions +ParseHeaders
> > Options +ExecCGI
> > </Location>
> > </IfModule>
> > --------
> >
> >
> > Thanks,
> >
> > Atsushi.
>
>
>
> ----- Original Message -----
> From: "Fredo Sartori" <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>
> Sent: Thursday, August 15, 2002 4:44 PM
> Subject: apache2, DBD/Oracle problem
>
>
> > Hi,
> >
> > I have problems connecting to an Oracle-DB through apache2. A test
> > script, that works fine when run directly from the shell, fails to
> > connect to the Orcacle DB, when run through apache. As far as I can see,
> > the SID is not passed to the DB server.
> >
> > .....................................
> > The foolowing message is found in the error log:
> >
> > [Thu Aug 15 09:25:46 2002] [error] 27824: ModPerl::Registry:
> > `DBI->connect(sun1) failed: Error while trying to retrieve text for
> > error ORA-12505 (DBD ERROR: OCIServerAttach) at
> > /server/apache-dev/perl/test-oracle line 23
> > '
> >
> > .......................................
> > the log file of the Oracle listener on the DB box ahows:
> >
> >
> > 15-AUG-02 09:25:46 *
> >
> (CONNECT_DATA=(SID=*)(SERVICE_NAME=sun1.)(CID=(PROGRAM=)(HOST=ollenhauer)(USER
> =www-adm))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.22.250.27)(PORT=53048)) sh * *
> * 0
> >
> > ......................................
> > The following versions are used:
> > apache 2.0.39
> > mod_perl: 1.99_04
> > DBI: 1.30
> > DBD-Oracle: 1.12
> >
> >
> > .................
> > #sample script:
> > #!/usr/local/bin/perl -w
> >
> > BEGIN {
> > $ENV{ORACLE_HOME} = '/opt/oracle/app/product/8.1.6';
> > $ENV{ORACLE_SID} = 'SPD';
> > }
> >
> > use CGI;
> >
> > use DBI;
> >
> >
> > my $db_name = 'dbi:Oracle:sun1';
> > my $db_user = 'webuser';
> > my $db_pass = 'topsecret';
> >
> > my $q=CGI->new;
> >
> > my $dbh = DBI->connect($db_name, $db_user, $db_pass,
> > {LongReadLen => 65534,
> > RaiseError => 1,
> > AutoCommit => 0});
> >
> > my $query = q{
> > SELECT count (*)
> > FROM asdb_mgr.stammdaten14
> > WHERE bis is null };
> >
> > my $sth = $dbh->prepare($query);
> >
> > my $rc = $sth->execute();
> >
> > my $count = $sth->fetchrow_array;
> >
> > print $q->header;
> > print "Anzahl der SPD-MdBs: $count\n";
> >
> > $sth->finish;
> >
> > $dbh->disconnect;
> >
> > ...............................
> > httpd.conf:
> >
> > Alias /perl /server/apache-dev/perl
> > <IfModule mod_perl.c>
> >
> > PerlRequire "/server/apache-dev/conf/perl-startup.pl"
> >
> > <Location /perl>
> > SetHandler perl-script
> > PerlResponseHandler ModPerl::Registry
> > PerlOptions +ParseHeaders
> > Options +ExecCGI
> > </Location>
> >
> > </IfModule>
> >
> > ......................................
> > perl-startup.sh:
> >
> > # file:perl-startup.pl
> > # ---------------
> > use Apache2 ();
> > use Apache::compat ();
> >
> > use lib qw(/server/apache-dev/perl/);
> >
> >
> > use ModPerl::Util ();
> > use Apache::RequestRec ();
> > use Apache::RequestIO ();
> > use Apache::RequestUtil ();
> >
> > use Apache::Server ();
> > use Apache::ServerUtil ();
> > use Apache::Connection ();
> > use Apache::Log ();
> >
> > use APR::Table ();
> >
> > use ModPerl::Registry ();
> >
> > use Apache::Const -compile => ':common';
> > use APR::Const -compile => ':common';
> >
> > 1;
> >
> > ..........................................
> >
> >
> > Thanks,
> >
> > Fredo
> >
> > --
> > Dr. Fredo Sartori Tel. 030-227-55061
> > SPD-Fraktion im Deutschen Bundestag FAX 030-227-56169
> > Platz der Republik e-mail: [EMAIL PROTECTED]
> > 11011 Berlin
> >
> >
