Perl, DBI, Oracle, Apache

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

I am at the end of my rope. I have created many perl scripts that draw data out of an Oracle Database. I can run these Scripts just fine on a Windows NT server running IIS and activestate perl. I also have a couple of RedHat Linux servers. These servers are going to be our web servers. I can get my scripts to run correctly and they run faster than on the NT server, but out of the blue it loses the Oracle.so or the libclntsh.so.8.0 files. At this point I can not do anything to fix it. I will try reinstalling the DBD::Oracle and DBI without any luck. I have shut down the Apache server and restarted it after different periods of time. I have also rebooted the machines(this is what we are trying to avoid with Linux) without any luck. The Error I am getting is:

Software error:
Can't load '/usr/lib/perl5/site_perl/5.6.0/i386-linux/auto/DBD/Oracle/Oracle.so' for module DBD::Oracle: libclntsh.so.8.0: cannot open shared object file: No such file or directory at /usr/lib/perl5/5.6.0/i386-linux/DynaLoader.pm line 200. at (eval 13) line 3
For help, please send mail to the webmaster (root@localhost <mailto:root@localhost>), giving this error message and the time and date of the error.

Software error:
at /usr/lib/perl5/5.6.0/CGI/Carp.pm line 346. Compilation failed in require at /usr/lib/perl5/5.6.0/Carp.pm line 119. For help, please send mail to the webmaster (root@localhost <mailto:root@localhost>), giving this error message and the time and date of the error.
I am doing this on a Dell Optiplex with RH7 installed. I also used cpan to upgrade everything. This error is being thrown from the connect string. All my database portions of my scripts are almost exactly out of the Programming the Perl DBI book.

#!/usr/bin/perl

use CGI;
use DBI;
use strict;
use CGI::Carp qw(fatalsToBrowser);
my $q = new CGI;
print $q->header;

my $dbh = DBI->connect("DBI:Oracle:host=myhost;sid=mysid", 'username',
+ 'password') or die "Connecting : $DBI::errstr\n";
#get the page settings
my $sth = $dbh->prepare("Select SYSDATE from dual");
$sth->execute;
print $sth->fetchrow_array;
[download]

I know this probably isn't a full Perl only question but I have exhausted all other means of looking for the answer.

Thanks

--BigJoe

Learn patience, you must.
Young PerlMonk, craves Not these things.
Use the source Luke.

Comment on Perl, DBI, Oracle, Apache Download Code

Replies are listed 'Best First'.
Re: Perl, DBI, Oracle, Apache by tadman (Prior) on Apr 24, 2001 at 23:19 UTC
The way your CGI compiled is different than mine, because there is nothing special about the line numbers that you are getting in the error message. When you see stuff like that, it is always helpful to view the appropriate line. With `vi`, you can always jump straight to it using a command line parameter: `$ vi +346 /usr/lib/perl5/5.6.0/CGI/Carp.pm` When dealing with shared libraries, though, make sure that your `/etc/ld.so.conf` contains the appropriate directories, especially any places where your Oracle driver might have dropped presents, and run `ldconfig` to apply the changes. Furthermore, make sure that your Web user (i.e. 'nobody') can read these libraries. The directory might be set to 0750 or something awful that prevents 'other' access to it. As a last resort, track down the missing file with the `find` command: `$ find / -name 'libclntsh.so.8.0*'` Just as a note, rebooting does not usually correct missing file problems. If your filesystem is somehow corrupted, rebooting may force a filesystem check, but this shouldn't have happened in the first place. It might also run 'ldconfig' which can fix the problem for you. As a general practice to "make things work", though, rebooting should be avoided.	[reply] [d/l] [select]
Re: Perl, DBI, Oracle, Apache by PsychoSpunk (Hermit) on Apr 24, 2001 at 23:20 UTC
In the readme for the DBD::Oracle module, it describes three or four environment variables that are necessary for the module to run properly (well, at least compile). Along with this, I am fairly certain that $ENV{LD_LIBRARY_PATH} needs to include the directory to the libclntsh.so file as listed. You may also be able to get away with running ldconfig. Anyway, you may want to check on the actual existence of the above shared library, as you may have libclntsh.so.X.0 where X may not be 8. I can't help you with Carp, but it seems like you should review the readme for DBD::Oracle to ensure all the proper environment variables are set. (Edit /etc/profile in RH7 to make them automatically available at every login). Carp may be dying on the error since the DBI generally sends out errors via $dbh->errstr. ALL HAIL BRAK!!!	[reply]
Re: Re: Perl, DBI, Oracle, Apache by BigJoe (Curate) on Apr 24, 2001 at 23:26 UTC
I do have the path set in the /etc/profile and in the httpd.conf file to allow the webserver to be able to access it. And the file is still there. If I run the standard script `servername/printenv` It does show the LD_LIBRARY_PATH still there. The file is also there and available to all users. --BigJoe Learn patience, you must. Young PerlMonk, craves Not these things. Use the source Luke.	[reply] [d/l]
Re: Re: Re: Perl, DBI, Oracle, Apache by BigJoe (Curate) on Apr 24, 2001 at 23:31 UTC
This is the print out of the /cgi-bin/printenv on the machine DOCUMENT_ROOT="/var/www/html" GATEWAY_INTERFACE="CGI/1.1" HTTP_ACCEPT="image/gif, image/x-xbitmap, image/jpeg, image/pjpeg, appl +ication/vnd.ms-powerpoint, application/vnd.ms-excel, application/mswo +rd, /" HTTP_ACCEPT_ENCODING="gzip, deflate" HTTP_ACCEPT_LANGUAGE="en-us" HTTP_CONNECTION="Keep-Alive" HTTP_HOST="172.16.8.19" HTTP_USER_AGENT="Mozilla/4.0 (compatible; MSIE 5.01; Windows NT 5.0)" LD_LIBRARY_PATH="/usr/local/OraHome/lib" ORACLE_HOME="/usr/local/OraHome" PATH="/sbin:/usr/sbin:/bin:/usr/bin:/usr/X11R6/bin" QUERY_STRING="" REMOTE_ADDR="172.16.X.XX" REMOTE_PORT="4978" REQUEST_METHOD="GET" REQUEST_URI="/cgi-bin/printenv" SCRIPT_FILENAME="/var/www/cgi-bin/printenv" SCRIPT_NAME="/cgi-bin/printenv" SERVER_ADDR="172.16.8.19" SERVER_ADMIN="root@localhost" SERVER_NAME="havoc" SERVER_PORT="80" SERVER_PROTOCOL="HTTP/1.1" SERVER_SIGNATURE="Apache/1.3.12 Server at havoc Port 80 \n" SERVER_SOFTWARE="Apache/1.3.12 (Unix) (Red Hat/Linux) mod_ssl/2.6.6 Op +enSSL/0.9.5a mod_perl/1.24" [download] --BigJoe Learn patience, you must. Young PerlMonk, craves Not these things. Use the source Luke.	[reply] [d/l]
Re: Perl, DBI, Oracle, Apache by arturo (Vicar) on Apr 24, 2001 at 23:29 UTC
but out of the blue it loses the Oracle.so or the libclntsh.so.8.0 files. Can you elaborate? I read this and hear "it works at one time and not at another", but the error messages suggest that something wasn't set up right. So, is it that the files are there at time A, and not there at time B? That's what it sounds like, and that would be VERY confusing. First stab: recall that when run as CGI scripts, these scripts have different environment varialbes available to them than when they're run from the command line. In particular, they DO NOT have `$ENV{ORACLE_HOME}` set. Set it explicitly in the script before you connect. Then try again. I'm assuming you installed the Oracle client libraries and symlinked libclntsh.so in a system lib directory (check `/etc/ld.so.conf` for a list of those; then do ln -s /usr/lib/libclnstsh.so.8.0 $ORACLE_HOME/lib/libclntsh.so.8.0</code> )). HTH	[reply] [d/l] [select]
Re: Re: Perl, DBI, Oracle, Apache by BigJoe (Curate) on Apr 24, 2001 at 23:38 UTC
The files are there at time A and they are there at time B. The permissions have not changed on the files or directories. The symlink is still there. --BigJoe Learn patience, you must. Young PerlMonk, craves Not these things. Use the source Luke.	[reply]
Re: Perl, DBI, Oracle, Apache by runrig (Abbot) on Apr 24, 2001 at 23:18 UTC
It sounds like DBD::Oracle was not installed correctly. Carp comes with perl, so it should have been installed correctly...unless perl wasn't installed correctly. BTW, what file is required at line 119?	[reply]
Re: Perl, DBI, Oracle, Apache by clemburg (Curate) on Apr 25, 2001 at 15:10 UTC
Have you tried using the -R linker switch or the LD_RUN_PATH environment variable at compile time for Oracle.so, and do these point to the right directories? This way, you can compile the path for the runtime linker into the output file of the compile time linking process. (Some of this is explained in the ld(1) manpage, I think.) BTW, will a "make test" for the module run through? Christian Lemburg Brainbench MVP for Perl http://www.brainbench.com	[reply]
Re: Perl, DBI, Oracle, Apache (possible answer) by BigJoe (Curate) on May 15, 2001 at 18:23 UTC
This is the reply I recieved from the Apache Mailling list from Rob Bloodgood. When I see this problem, I automatically think, Oh, the Oracle libs aren't being located by the system. Edit /etc/ld.so.conf and add the value of $ORACLE_home/lib (the directory that has libclntsh.so.8 in it) eg /usr/local/oracle/8.1.5/lib Then run /sbin/ldconfig to update Linux's idea of where things are, and stop and start the server. ALSO, ensure that ORACLE_HOME and preferably ORACLE_SID are explicitly provided to your perl stuff: in httpd.conf PerlPassEnv ORACLE_HOME THIS CAN BITE YOU! If your httpd startup script doesn't have the oracle environment loaded, you may have to fix that as well: at the top of /etc/rc.d/init.d/httpd # Source function library. . /etc/rc.d/init.d/functions <!-- already there --> # Source Oracle environment <!-- you add these lines --> ORAENV_ASK=NO ORACLE_SID=stats . /usr/bin/oraenv # See how we were called. <!-- already there --> case "$1" in --BigJoe Learn patience, you must. Young PerlMonk, craves Not these things. Use the source Luke.	[reply]