oracle_home, oci8 and stored procedure

[Ben Fitzgerald]

Hi

I'm having an irritating problem.

I've got php installed with the compile-time flag --oci8=$ORACLE_HO ME.

I can make connections to the oracle db no problem. Updating, selecting etc.
is all good.

However, when I use a web page that calls a stored procedure:

$stmt = OCIParse($conn, "begin :result := s30.fn_udicheck ('$UDI'); end;");
OCIBindByName($ stmt,":result", &$result,32) ;
OCIExecute($stm t);

I get the following error when viewing the php page in a browser with apache
as the web server:

Warning: ociexecute(): OCIStmtExecute: ORA-06550:
line 1, column 7: PLS-00553: character set name is not recognized
ORA-06550: line 0, column 0: PL/SQL: Compilation unit analysis terminated

My problem is related to the variable $ORACLE_HOME. When I cat my php file
through php from the command line with $ORACLE_HOME set it works. When I
unset $ORACLE HOME I get the error above.

me> export
....
declare -x ORACLE_HOME="/u01/app/oracle/product/8.1.7"
....

me> cat myfile.php | /usr/bin/php * this works *

me> unset $ORACLE_HOME

me> cat myfile.php | /usr/bin/php * this now fails *

I've set $ORACLE_HOME in httpd.conf, php.ini and in the script
itself with putenv. I've also made sure the webserver uid (apache)
has access to all files under $ORACLE_HOME. This all makes no difference.

I've recompiled with LD_LIBRARY_PATH =$ORACLE_HOME/lib too. No joy :(

I hope someone out there can help as this is very frustrating!

Thanks in advance,

--
Ben F

Registered Linux user number 339435

[Seb]

Re: oracle_home, oci8 and stored procedure

Hi

Did you check the environnement variables through phpinfo ? Is
ORACLE_HOME correctly set and do you need some NLS variable ?

Did you check these variables on server side ?

Seb

Ben Fitzgerald wrote:
[color=blue]
> Hi
>
> I'm having an irritating problem.
>
> I've got php installed with the compile-time flag --oci8=$ORACLE_HO ME.
>
> I can make connections to the oracle db no problem. Updating, selecting etc.
> is all good.
>
> However, when I use a web page that calls a stored procedure:
>
> $stmt = OCIParse($conn, "begin :result := s30.fn_udicheck ('$UDI'); end;");
> OCIBindByName($ stmt,":result", &$result,32) ;
> OCIExecute($stm t);
>
> I get the following error when viewing the php page in a browser with apache
> as the web server:
>
> Warning: ociexecute(): OCIStmtExecute: ORA-06550:
> line 1, column 7: PLS-00553: character set name is not recognized
> ORA-06550: line 0, column 0: PL/SQL: Compilation unit analysis terminated
>
> My problem is related to the variable $ORACLE_HOME. When I cat my php file
> through php from the command line with $ORACLE_HOME set it works. When I
> unset $ORACLE HOME I get the error above.
>
> me> export
> ...
> declare -x ORACLE_HOME="/u01/app/oracle/product/8.1.7"
> ...
>
> me> cat myfile.php | /usr/bin/php * this works *
>
> me> unset $ORACLE_HOME
>
> me> cat myfile.php | /usr/bin/php * this now fails *
>
> I've set $ORACLE_HOME in httpd.conf, php.ini and in the script
> itself with putenv. I've also made sure the webserver uid (apache)
> has access to all files under $ORACLE_HOME. This all makes no difference.
>
> I've recompiled with LD_LIBRARY_PATH =$ORACLE_HOME/lib too. No joy :(
>
> I hope someone out there can help as this is very frustrating!
>
> Thanks in advance,
>[/color]

[Seb]

Re: oracle_home, oci8 and stored procedure

[color=blue]
> I've set $ORACLE_HOME in httpd.conf, php.ini and in the script
> itself with putenv. I've also made sure the webserver uid (apache)
> has access to all files under $ORACLE_HOME. This all makes no difference.
>[/color]

It is not where where you should set it. $ORACLE_HOME should be set in
the startup script of apache either in /etc/init.d/apache (or else) or
in your apachectl script.

You can also set it in the .profile or .bashrc (or else) of the user who
start the apache process.

That should be better. It has nothing to do with httpd.conf or php.ini.

Seb

[Ben Fitzgerald]

Re: oracle_home, oci8 and stored procedure

In article <c1581l$m4n$1@r eader1.imaginet .fr>, Seb wrote:[color=blue]
>[color=green]
>> I've set $ORACLE_HOME in httpd.conf, php.ini and in the script
>> itself with putenv. I've also made sure the webserver uid (apache)
>> has access to all files under $ORACLE_HOME. This all makes no difference.
>>[/color]
>
> It is not where where you should set it. $ORACLE_HOME should be set in
> the startup script of apache either in /etc/init.d/apache (or else) or
> in your apachectl script.
>
> You can also set it in the .profile or .bashrc (or else) of the user who
> start the apache process.
>
> That should be better. It has nothing to do with httpd.conf or php.ini.
>
> Seb[/color]

Seb

Thank you!

That did the trick!

I had ORACLE_HOME exported in /etc/profile but this wasn't read on starting
httpd using /sbin/service httpd restart (redhat 8.0).

I've now sourced it in /etc/init.d/httpd

I need to read up more clearly on when different files are read and not read
e.g. .bashrc /etc/profile and by what shells, then I won't have all this
messing around again.

Thanks very much for your help,


--
Ben F

Registered Linux user number 339435