[ back to toc ]

DBI/DBD:Oracle

Date: 2002/04/23 10:04

Q:
Hello,

I'm using Apache on Red Hat Linux7.1, Perl 5.6.0, the latest DBI and
DBD::Oracle,
and Oracle9i client intalled to access the Oracle9i database in the
windows2000 machine.
I can connect to the Oracle database and retrieve a result set from the
Linux command line
with 'perl dbicgi.cgi'(code is below). But I can not from a web page
although plain CGI/Perl works
file. The .bash_profile used to set up environment variable is also
listed.That means, I've already set up the
ORACLE_HOME environment variable.

However, the error_log shows:
ORACLE_HOME environment variable not set!
DBI->connect(host=160.129.24.199;sid=Marray;port=1521) failed: (UNKNOWN
OCI STATUS 1804) OCIInitialize. Check
ORACLE_HOME and NLS settings etc. at /var/www/cgi-bin/Testcgi/dbicgi.cgi
line 17
Couldn't connect: (UNKNOWN OCI STATUS 1804) OCIInitialize. Check
ORACLE_HOME and NLS settings etc. at /var/www
/cgi-bin/Testcgi/dbicgi.cgi line 17.

It seems that I need extra configuration on the Apache server.
Could you help me solve this problem?
I shall appreciate very much for your help!

many thanks!
Ying

[ying@paqman Testcgi]$ cat dbicgi.cgi
#!/usr/bin/perl -w

use DBI;
use DBD::Oracle;
use CGI;

print "Content-type: text/html\n\n";

my $dbsource="dbi:Oracle:host=DBserver;sid=Marray;port=1521";
my $user="scott";
my $passwd="tiger";
my $dbh;
my $sth;
my @data;
my $deptname;

$dbh=DBI->connect($dbsource, $user, $passwd);

$statement=qq(select * from dept);
$sth=$dbh->prepare($statement);
$sth->execute;

print "<html><head><title>Database web test</title></head><body>";
print "Database web test!";
print "<TABLE BORDER=1>";

while(@row=$sth->fetchrow_array())
{
print "<TR><TD>", $row[0], "</TD><TD>", $row[1], "</TD><TD>",
$row[2],
"</TD><TR>";
}

print "</TABLE>";

$dbh->disconnect;
print "</body></html>\n";

ng@paqman ying]$ more .bash_profile
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin
BASH_ENV=$HOME/.bashrc

export BASH_ENV PATH
unset USERNAME

#new entries in this file
umask 022

#SET UP ORACLE ENVIRONMENT
export TWO_TASK=test.mc.vanderbilt.edu
export ORACLE_BASE=/home/oracle
export ORACLE_HOME=/home/oracle/product/9.0.1

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib:/usr/local/lib
export TNS_ADMIN=$ORACLE_HOME/network/admin
export NLS_LANG=AMERICAN
export ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data
export ORACLE_OWNER=oracle
export ORACLE_TERM=xterm
export ORACLE_USERID=scott/tiger@test.mc.vanderbilt.edu

#SETUP SEARCH PATH
PATH=$PATH:$ORACLE_HOME/bin
export PATH

#SETUP JAVA ENVIRONMENT
export JAVA_HOME=/usr/local/java
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
CLASSPATH=$CLASSPATH:$ORACLE_HOME/network/jlib

"question.txt" 103L, 2818C written
[ying@paqman ying]$ cat qu*
Hello,

I'm using Apache on Red Hat Linux7.1, Perl 5.6.0, the latest DBI and
DBD::Oracle,
and Oracle9i client intalled to access the Oracle9i database in the
windows2000 machine.
I can connect to the Oracle database and retrieve a result set from the
Linux command line
with 'perl dbicgi.cgi'(code is below). But I can not from a web page
although plain CGI/Perl works
file. The .bash_profile used to set up environment variable is also
listed.That means, I've already set up the ORACLE_HOME environment
variable.

However, the error_log shows:
ORACLE_HOME environment variable not set!
DBI->connect(host=160.129.24.199;sid=Marray;port=1521) failed: (UNKNOWN
OCI STATUS 1804) OCIInitialize. Check
ORACLE_HOME and NLS settings etc. at /var/www/cgi-bin/Testcgi/dbicgi.cgi
line 17
Couldn't connect: (UNKNOWN OCI STATUS 1804) OCIInitialize. Check
ORACLE_HOME and NLS settings etc. at /var/www
/cgi-bin/Testcgi/dbicgi.cgi line 17.

It seems that I need extra configuration on the Apache server.
Could you help me solve this problem?
I shall appreciate very much for your help!

many thanks!
Ying

[ying@paqman Testcgi]$ cat dbicgi.cgi
#!/usr/bin/perl -w

use DBI;
use DBD::Oracle;
use CGI;

print "Content-type: text/html\n\n";

my $dbsource="dbi:Oracle:host=DBserver;sid=Marray;port=1521";
my $user="scott";
my $passwd="tiger";
my $dbh;
my $sth;
my @data;
my $deptname;

$dbh=DBI->connect($dbsource, $user, $passwd);

$statement=qq(select * from dept);
$sth=$dbh->prepare($statement);
$sth->execute;

print "<html><head><title>Database web test</title></head><body>";
print "Database web test!";
print "<TABLE BORDER=1>";

while(@row=$sth->fetchrow_array())
{
print "<TR><TD>", $row[0], "</TD><TD>", $row[1], "</TD><TD>",
$row[2],
"</TD><TR>";
}

print "</TABLE>";

$dbh->disconnect;
print "</body></html>\n";

ng@paqman ying]$ more .bash_profile
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin
BASH_ENV=$HOME/.bashrc

export BASH_ENV PATH
unset USERNAME

#new entries in this file
umask 022

#SET UP ORACLE ENVIRONMENT
export TWO_TASK=test.mc.vanderbilt.edu
export ORACLE_BASE=/home/oracle
export ORACLE_HOME=/home/oracle/product/9.0.1

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib:/usr/local/lib
export TNS_ADMIN=$ORACLE_HOME/network/admin
export NLS_LANG=AMERICAN
export ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data
export ORACLE_OWNER=oracle
export ORACLE_TERM=xterm
export ORACLE_USERID=scott/tiger@test.mc.vanderbilt.edu

#SETUP SEARCH PATH
PATH=$PATH:$ORACLE_HOME/bin
export PATH

#SETUP JAVA ENVIRONMENT
export JAVA_HOME=/usr/local/java
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
CLASSPATH=$CLASSPATH:$ORACLE_HOME/network/jlib

A:
To run the ORACLE module you need some environment variables being set.
This is configured by the system manager for you when you run the program
from the command line. However CGI programs run in a different context.
You have two choices:

1. Ask the system manager to configure the Apache user environment so that
the ORACLE environment variables are set.

2. Insert some lines, like:

$ENV{ORACLE_HOME} = "/opt/oracle/app/oracle/product/8.0.5";
$ENV{ORACLE_SID} = "WINDPR";
$ENV{NLS_LANG} = "AMERICAN_HUNGARY.EE8ISO8859P2";

at the start of your CGI program.

The solution #1 is slower, but if ever the setup changes you need not
alter all your codes.

Regards,
Peter

[ back to toc ]