Shell parameter passing to stored procedure

Hi List
I'm trying to call a 9i stored procedure inside a bash script passing it a parameter. I have the following code: (it must be run by root because of other commands on script)

#./run_proc 15032002

#!/bin/bash
# the run_proc script
PROC_DATE=$1
export PROC_DATE
su - oracle -c "PROC_DATE=$PROC_DATE sqlplus /nolog @exec_procedure.sql"

Inside @exec_procedure I have:

connect user/password_at_inst
spool file_${PROC_DATE}.log # here it works... execute myprocedure ($PROC_DATE) # here it doesn't exit

On the spool clause the substitution occurs and I got a file named "file_15032002.log", but the execute clause does not get substituted. The problem is that I don't know how to pass the argument to the stored procedure. The opened sql session try to interpret the $PROC_DATE inside its parsing step and gave me an error.

I really need help on this. I will be very glad for any kind of information

Thanks in advance

Oswaldo Castro Received on Sat Mar 16 2002 - 13:14:42 CST