Problem with DBD::Oracle in cgi-script

Hello,
I'm trying to make an Oracle 10g EE DB accessible via web-interface.
The perl-scripts that are run from console do work properly, but I don't
achieve in connecting to the database from the cgi-scripts.

Does anybody have a clue where I have to look?
Apache is working, perl is working, dynamic sites are working, DB is
working but all the four together seem to have difficulties :-(

System is Debian stable.

TIA

Marc

Re: Problem with DBD::Oracle in cgi-script

Marc F. Neininger wrote:
> Hello,
> I'm trying to make an Oracle 10g EE DB accessible via web-interface.
> The perl-scripts that are run from console do work properly, but I don't
> achieve in connecting to the database from the cgi-scripts.
>
> Does anybody have a clue where I have to look?

Check the error message(s) in the server's error log.

> Apache is working, perl is working, dynamic sites are working, DB is
> working but all the four together seem to have difficulties :-(

Permissions problem, perhaps? The CGI scripts may be run as some other
user than when you run Perl from command line (unless suEXEC or
something like it has been enabled).

--
Gunnar Hjalmarsson
Email: http://www.gunnar.cc/cgi-bin/contact.pl

Re: Problem with DBD::Oracle in cgi-script

Marc F. Neininger wrote:
> I'm trying to make an Oracle 10g EE DB accessible via web-interface.
> The perl-scripts that are run from console do work properly, but I don't
> achieve in connecting to the database from the cgi-scripts.
>
> Does anybody have a clue where I have to look?
> Apache is working, perl is working, dynamic sites are working, DB is
> working but all the four together seem to have difficulties :-(
>
> System is Debian stable.


It's an odds-on bet that the problem is environment variables.

Database + "works at command line" + "does not work in web"
== environment.

Look up PassEnv and SetEnv in Apache - equivalents elsewhere.

--
Jonathan Leffler #include
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2007.0826 -- http://dbi.perl.org/

Re: Problem with DBD::Oracle in cgi-script

Jonathan Leffler schrieb:

> It's an odds-on bet that the problem is environment variables.
>
> Database + "works at command line" + "does not work in web"
> == environment.
>
> Look up PassEnv and SetEnv in Apache - equivalents elsewhere.

That's one of the problems. For Oracle you have to set the ORACLE_SID
and ORACLE_HOME variables for the calling shell (aka Apache Environment).

I set those variables via $Env{ORACLE_SID} und $Env{ORACLE_HOME} in the
script before connecting to the DB but the problem might be deeper
inside. The driver links against an Oracle lib that is not found at
runtime. So I compiled the driver another time and got some mistakes like

....
Constant subroutine _POSIX_C_SOURCE undefined at
/usr/lib/perl/5.8/features.ph line 50.
Constant subroutine _XOPEN_SOURCE undefined at
/usr/lib/perl/5.8/features.ph line 52.
Constant subroutine _XOPEN_SOURCE_EXTENDED undefined at
/usr/lib/perl/5.8/features.ph line 54.
.... .

But the problem seems to be a lib. Now I'm looking which one it might
be. It looks to me as if I would link against some outdated libraries.
But the system is set up 3 weeks ago and patched. :-(

I ignored those errorlines and installed the driver eventhough.
Afterwards I was (and still am) able to connect to the DB via Script and
question the DB all stuff. But with the same set of commands in a script
that is executed via Apache I get the following error output:

install_driver(Oracle) failed: Can't load
'/usr/local/lib/perl/5.8.8/auto/DBD/Oracle/Oracle.so' for module
DBD::Oracle: libclntsh.so.10.1: cannot open shared object file: No such
file or directory at /usr/lib/perl/5.8/DynaLoader.pm line 225.
at (eval 4) line 3 .

Sorry for my confusing answer but I really am confused...

Marc

Re: Problem with DBD::Oracle in cgi-script

On Wed, 29 Aug 2007 10:17:32 +0200, Marc F. Neininger wrote:
> But the problem seems to be a lib. Now I'm looking which one it might
> be. It looks to me as if I would link against some outdated libraries.
> But the system is set up 3 weeks ago and patched. :-(
>
> I ignored those errorlines and installed the driver eventhough.
> Afterwards I was (and still am) able to connect to the DB via Script and
> question the DB all stuff. But with the same set of commands in a script
> that is executed via Apache I get the following error output:
>
> install_driver(Oracle) failed: Can't load
> '/usr/local/lib/perl/5.8.8/auto/DBD/Oracle/Oracle.so' for module
> DBD::Oracle: libclntsh.so.10.1: cannot open shared object file: No such
> file or directory at /usr/lib/perl/5.8/DynaLoader.pm line 225.
> at (eval 4) line 3 .

Just passing by. Maybe check LD_LIBRARY_PATH and LD_RUN_PATH?

--
Peter Scott
http://www.perlmedic.com/
http://www.perldebugged.com/

Re: Problem with DBD::Oracle in cgi-script

Peter Scott wrote:

>> install_driver(Oracle) failed: Can't load
>> '/usr/local/lib/perl/5.8.8/auto/DBD/Oracle/Oracle.so' for module
>> DBD::Oracle: libclntsh.so.10.1: cannot open shared object file: No such
>> file or directory at /usr/lib/perl/5.8/DynaLoader.pm line 225.
>> at (eval 4) line 3 .
>
> Just passing by. Maybe check LD_LIBRARY_PATH and LD_RUN_PATH?
>

I set both variables to $ORACLE_HOME/lib.
Even hard coded it in the Makefile.PL .

What I really do not understand is that the driver works fine with
scripts that are executed from the shell...

Thanks for the answers

Marc

Re: Problem with DBD::Oracle in cgi-script

On 08/29/2007 03:17 AM, Marc F. Neininger wrote:
>
> That's one of the problems. For Oracle you have to set the ORACLE_SID
> and ORACLE_HOME variables for the calling shell (aka Apache Environment).
>
> I set those variables via $Env{ORACLE_SID} und $Env{ORACLE_HOME} in the
> script before connecting to the DB but the problem might be deeper
> inside. The driver links against an Oracle lib that is not found at
> runtime. So I compiled the driver another time and got some mistakes like
> [...]

Probably those variables must be set at compile time rather than run time:

BEGIN {
$ENV{ORACLE_SID} = "...";
$ENV{ORACLE_HOME} = "....";
}
use DBD::Oracle;
use DBI;

However, setting the variables in the Apache config file is the Right
Way(TM).

Re: Problem with DBD::Oracle in cgi-script

Marc F. Neininger wrote:
> Jonathan Leffler schrieb:
>
>> It's an odds-on bet that the problem is environment variables.
>>
>> Database + "works at command line" + "does not work in web"
>> == environment.
>>
>> Look up PassEnv and SetEnv in Apache - equivalents elsewhere.
>
> That's one of the problems. For Oracle you have to set the ORACLE_SID
> and ORACLE_HOME variables for the calling shell (aka Apache Environment).
>
> I set those variables via $Env{ORACLE_SID} und $Env{ORACLE_HOME} in the
> script before connecting to the DB but the problem might be deeper
> inside. The driver links against an Oracle lib that is not found at
> runtime. So I compiled the driver another time and got some mistakes like
>
> ...
> Constant subroutine _POSIX_C_SOURCE undefined at
> /usr/lib/perl/5.8/features.ph line 50.
> Constant subroutine _XOPEN_SOURCE undefined at
> /usr/lib/perl/5.8/features.ph line 52.
> Constant subroutine _XOPEN_SOURCE_EXTENDED undefined at
> /usr/lib/perl/5.8/features.ph line 54.
> ... .
>
> But the problem seems to be a lib. Now I'm looking which one it might
> be. It looks to me as if I would link against some outdated libraries.
> But the system is set up 3 weeks ago and patched. :-(
>
> I ignored those errorlines and installed the driver eventhough.
> Afterwards I was (and still am) able to connect to the DB via Script and
> question the DB all stuff. But with the same set of commands in a script
> that is executed via Apache I get the following error output:
>
> install_driver(Oracle) failed: Can't load
> '/usr/local/lib/perl/5.8.8/auto/DBD/Oracle/Oracle.so' for module
> DBD::Oracle: libclntsh.so.10.1: cannot open shared object file: No such
> file or directory at /usr/lib/perl/5.8/DynaLoader.pm line 225.
> at (eval 4) line 3 .
>
> Sorry for my confusing answer but I really am confused...
>
> Marc

What is your LD_LIBRARY_PATH set to when this is working? It may not be
getting set the same when apache tries running the script.

Jeff Long

Re: Problem with DBD::Oracle in cgi-script

Mumia W. wrote:

> However, setting the variables in the Apache config file is the Right
> Way(TM).
>
Yes. I did it and I could change the error message twice :-)
First I found out that there was no read acces for World and the Error
message changed.

Afterwards the Error Message changed to "ERROR OCIEnvNlsCreate. Check
ORACLE_HOME env var, NLS settings, permissions, etc."

I set the ENV in the Apache conf. file via

SetEnv LD_LIBRARY_PATH /u01/app/oracle/lib
SetEnv ORACLE_HOME /u01/app/oracle
SetEnv ORACLE_SID ORAPARA
SetEnv NLS_LANG AMERICAN_AMERICA.WE8ISO8859P1
SetEnv NLS_CHARACTERSET WE8ISO8859P1
SetEnv NLS_NCHAR_CHARACTERSET AL16UTF16

and looked that the parameters fit to the databases NLS_values (SELECT *
from NLS_DATABASE_PARAMETERS;)
..

But no luck.

Till tomorrow and Thanks for all the hints

Marc

Re: Problem with DBD::Oracle in cgi-script

On Wed, 29 Aug 2007 14:03:07 +0200, Marc F. Neininger wrote:
> Peter Scott wrote:
>
>>> install_driver(Oracle) failed: Can't load
>>> '/usr/local/lib/perl/5.8.8/auto/DBD/Oracle/Oracle.so' for module
>>> DBD::Oracle: libclntsh.so.10.1: cannot open shared object file: No such
>>> file or directory at /usr/lib/perl/5.8/DynaLoader.pm line 225.
>>> at (eval 4) line 3 .
>>
>> Just passing by. Maybe check LD_LIBRARY_PATH and LD_RUN_PATH?
>>
>
> I set both variables to $ORACLE_HOME/lib.
> Even hard coded it in the Makefile.PL .

Yes, but are they set when the CGI program runs?

I suggest you rewrite your Makefile.PL and link line to do without them.
I consider those variables evil and ban anyone working for me from using
them. They have been the source of countless annoying problems, and this
is starting to smell like one of them.

--
Peter Scott
http://www.perlmedic.com/
http://www.perldebugged.com/

Re: Problem with DBD::Oracle in cgi-script

Marc F. Neininger wrote:
> Mumia W. wrote:
>
>> However, setting the variables in the Apache config file is the Right
>> Way(TM).
>>
> Yes. I did it and I could change the error message twice :-)
> First I found out that there was no read acces for World and the Error
> message changed.
>
> Afterwards the Error Message changed to "ERROR OCIEnvNlsCreate. Check
> ORACLE_HOME env var, NLS settings, permissions, etc."
>
> I set the ENV in the Apache conf. file via
>
> SetEnv LD_LIBRARY_PATH /u01/app/oracle/lib
> SetEnv ORACLE_HOME /u01/app/oracle
> SetEnv ORACLE_SID ORAPARA
> SetEnv NLS_LANG AMERICAN_AMERICA.WE8ISO8859P1
> SetEnv NLS_CHARACTERSET WE8ISO8859P1
> SetEnv NLS_NCHAR_CHARACTERSET AL16UTF16
>
> and looked that the parameters fit to the databases NLS_values (SELECT *
> from NLS_DATABASE_PARAMETERS;)
> .

You may have to set the environment variables in apache/bin/envvars. I
can't remember the circumstances though that require that.

Jeff Long

Re: Problem with DBD::Oracle in cgi-script

Peter Scott schrieb:
> On Wed, 29 Aug 2007 14:03:07 +0200, Marc F. Neininger wrote:
>> Peter Scott wrote:
>>
>>>> install_driver(Oracle) failed: Can't load
>>>> '/usr/local/lib/perl/5.8.8/auto/DBD/Oracle/Oracle.so' for module
>>>> DBD::Oracle: libclntsh.so.10.1: cannot open shared object file: No such
>>>> file or directory at /usr/lib/perl/5.8/DynaLoader.pm line 225.
>>>> at (eval 4) line 3 .
>>> Just passing by. Maybe check LD_LIBRARY_PATH and LD_RUN_PATH?
>>>
>> I set both variables to $ORACLE_HOME/lib.
>> Even hard coded it in the Makefile.PL .
>
> Yes, but are they set when the CGI program runs?
>
> I suggest you rewrite your Makefile.PL and link line to do without them.
> I consider those variables evil and ban anyone working for me from using
> them. They have been the source of countless annoying problems, and this
> is starting to smell like one of them.
>

Hi Peter,

I refreshed the DBD::Oracle package and rebuilt it. I will have to look
how a patch is created as the very first error message is just that the
version string of Debian's perl installation contains a point and an
underscore...

The problem with the "not found" lib was solved as I had to "755" the
lib directory (was I think 710 by default). I wrote out the whole
environment of the shell I was able to work with my scheme and rebuilt
the whole environment via SetEnv policy for the apache alias.

Thanks for your answer

Marc