Benbart,

I guess you are trying to assign a value to a UNIX variable from within
SQL Plus, right? We had done something similar to capture SQL error code
and error message after executing an Oracle procedure from SQL Plus.

In the following example, we pass 4 arguments to the Shell script
1. Date of running the job
2. DB username
3. DB password
4. TNS name

The script then calls SQL Plus and executes a procedure
ENT_PURGE_PKG.PURGE_TABLE_PRC, passing the run date as an input
parameter and retrieving the SQL error code and error message (OUT
parameters in the procedure).

After the script executes, the values of UNIX variables RETURN_CD and
RETURN_MSG are set.

#!/usr/bin/ksh

# This Script aims at doing the following activities
# 1. Execute the procedure for Purging

# Parameters
RUNDATE=$1
USERNAME=$2
PASSWORD=$3
SERVER=$4

sqlplus -S ${USERNAME}/${PASSWORD}@${SERVER} |&

print -p -- 'set feed off pause off pages 0 head off veri off line 500'
print -p -- 'set term off time off'
print -p -- 'var return_cd NUMBER'
print -p -- 'var return_msg VARCHAR2(300)'
print -p -- "set sqlprompt ''"

print -p -- "EXEC
ent_purge_pkg.purge_table_prc(TO_DATE($RUNDATE,'YYYYMMDD'), :return_cd,
:return_msg);"

print -p -- "SELECT :return_cd FROM dual;"
read -p RETURN_CD

print -p -- "SELECT :return_msg FROM dual;"
read -p RETURN_MSG

print -p -- exit

echo RETURN CODE : $RETURN_CD
echo RETURN MESSAGE : $RETURN_MSG

if [ $RETURN_CD -ne 0 ] ; then
exit 1
fi

exit 0

Hope this helps.

Regards,
Rahul Dutta

-----Original Message-----
From: Benbart
[mailto:oracledba-ezmlmshield-x82020855.[Email address protected]
Sent: Thursday, June 30, 2005 12:22 PM
To: LazyDBA Discussion
Subject: UNIX variable of function name

Hi all,
I just want to know if there is a UNIX variable to get the function name
within the script everytime that function is called in the script ...
UNIX flavor is HP-UX .... i.e. before I go about hard-coding my script
to check what and when a function is called within the script ...



--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html


Oracle LazyDBA home page