Sqlplus with shell_exec(); PHP command

Shell Programming and Scripting
1

Hi,

I need to run a PL/SQL Query from a distant oracle server in order to create spool files and send it to my own server, using a php script.

I firstly created a SH script called myscript.sh

#!/bin/bash

echo "This script is working"
sqlplus username/password//ip_address_distant_server:port/database @/var/www/mydirectory/myquery.sql parameter1 parameter2 parameter3

let's say that my PHP script is like :

shell_exec('sh var/www/mydirectory/myscript.sh');

Note that the sql file myquery.sql does contain spools that are supposed to send files to my own server...
When i run the script from the command line, it works. I use an normal user (not root) to make it run When i run it from the php page, it doesn't work. Apache use www-data to run it, so i made sure to include the www-data group to the script. Note that i tried running

shell_exec('ls -l');

and that it works like a charm! i also tried to run another sh script without sqlplus command, and it also work.

I'm suspecting a permission problem with the sqlplus command, because the only difference between both methods to run the script is the user running it. But i don't know how to change user permissions to a command, or a possible escape to make it work. I'm out of ideas i tried several things such as exec(); or system(); instead of shell_exec(); and changing chown and chmod to both sql / sh files

Any help would be really great, thanks :smiley:

2

Perhaps the relative path is the problem.

Regards and welcome to the forum,
Alister

3

Thank you Alister for the quick reply,

i don't think the relative path is the problem, because if i run the php script with shell_exec('sh /var/www/directory/script.sh');

with script.sh being :

#!/bin/bash

echo "This script is working"
sqlplus username/password//ip_address_distant_server:port/database @/var/www/mydirectory/myquery.sql parameter1 parameter2 parameter3

the echo command works, it will display "This script is working"
but the sql query does not run...it clearly shows that the php is not the problem, and i believe the problem is in the permission apache server user has to use sqlplus unix command.
I did not found enough information on the internet about this command (in oracle doc it really is looking for a needle in a haystack)...

thank you anyway !

4

Ah, but that one does not have a relative path.

"var/path/to/whatever" means "start looking at the current directory", which may be the directory the PHP script is in.

"/var/path/to/whatever" means "Start looking in the root directory", making the current folder not matter.

5

Absolute path calls and getting all the Oracle environment variables, PATH, LD_LIBRARY_PATH set right for sql*plus in the wrapper. Interactively, it inherits all yours.

6

My mistake is in the first post where i posted var/www/mydirectory/myscript.sh instead of /var/www/mydirectory/myscript.sh. But basically, both ways would lead in the same directory as var is placed in the root...and anyway, as i said before, the bash script call is working just fine (the echo command works and prompt the text i want) its really just the sqlplus command not working.

I am not sure i have understood what you told me, sorry ;
1) If you are telling me i need the absolute path, i think /var/www/mydirectory/mysqlquery.sql is absolute path... right ?
2) Is there any special code to include to get the oracle environment variables ?

7

If you don't do so yourself, chances are your /etc/profile or your own ~/.profile or equivalent does so for you. Check the contents of those to see what they do, or just load /etc/profile itself like I suggested above.

8

On more desperate days, you can provide the daemon process your interactive env:

set | sed 's/^[a-zA-Z0-9_]*=/export &/' >final_env

and dot final_env in the same shell before trying to run sqlplus, probably in a subshell to be safe!

bash -c 'cd /your_home ; . final_env ; sqlplus . . . . '
9

Just add the following in your script (myscript.sh):

ORACLE_HOME=<value from the working env>
export ORACLE_HOME
"$ORACLE_HOME"/bin/sqlplus ...<rest of your commands>