Shell Programming and Scripting

I have a ksh script that connects to sqlplus and dumps the query results into a file. The script works file when I run it from the command line, however, when I call it from PHP using system(), exec(), or shell_exec() commands, the script doesn't seem to run the query. It will create the text file but nothing's in it. I've used 'whoami' to verify the user is the same as I'm logging in with. Any ideas? Any help would be much appreciated.

Jeff

Here's ksh script (actual sql replaced for brevity)
#!/bin/ksh

# Passed Parameters:
site=$1

# Output file name:
OUTPUT_FILE=results-${site}.txt

# initiate sqlplus with silent option
sqlplus -s usr/pwd <<!>${OUTPUT_FILE}
whenever sqlerror exit 2
set heading off
set pages 0
set linesize 195
set tab on
set feedback off
set verify off

select * from app_user
/
!

exit 0

It's probably not running as an interactive shell so therefore, you need to obtain your Oracle environment variables explicitly.

Code:

#!/bin/ksh


export ORACLE_SID=$ORACLE_SID
ORAENV_ASK=NO
. /usr/local/bin/oraenv
ORAENV_ASK=


# Passed Parameters:
site=$1

# Output file name:
OUTPUT_FILE=results-${site}.txt

# initiate sqlplus with silent option
sqlplus -s usr/pwd <<!>${OUTPUT_FILE}
whenever sqlerror exit 2
set heading off
set pages 0
set linesize 195
set tab on
set feedback off
set verify off

select * from app_user
/
!

exit 0

Thanks, that helped. However, I am now getting the following error:

ERROR:
ORA-01034: ORACLE not available
ORA-27123: unable to attach to shared memory segment
SVR4 Error: 13: Permission denied


Enter user-name: SP2-0306: Invalid option.
Usage: CONN[ECT] [logon] [AS {SYSDBA|SYSOPER}]
where <logon> ::= <username>[/<password>][@<connect_string>] | /
Enter user-name: SP2-0306: Invalid option.
Usage: CONN[ECT] [logon] [AS {SYSDBA|SYSOPER}]
where <logon> ::= <username>[/<password>][@<connect_string>] | /
SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus

The user that the script is running as has permission to all of the Oracle directories. Anyone know what's wrong?

Thanks again for any help.

What is happening is Oracle is trying to find a shared memory segment that matches your Oracle SID value. One of two things is happening, 1) you database is not online or 2) your $ORACLE_SID value is wrong. I'm sure that you know whether your database is running so have your DBA confirm your $ORACLE_SID value.

Oh yeah, perhaps I should mention that this runs from the command line. It's when I call the script from a php page that it gets the error.

Your php session still needs to have its parameters passed correctly. For grins, try addind your SID to the sqlplus command and see what happens:

Code:

sqlplus un/ps@db

You sir, are the man! Where do I send the 6-pack?