Home » SQL & PL/SQL » SQL & PL/SQL » Calling PL/SQL stored procedure from shell
Calling PL/SQL stored procedure from shell [message #38059] Sat, 16 March 2002 02:27 Go to next message
Oswaldo Castro
Messages: 2
Registered: March 2002
Junior Member
Hi List
I'm trying to call a 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@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
Re: Calling PL/SQL stored procedure from shell [message #38080 is a reply to message #38059] Mon, 18 March 2002 08:09 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Try adding quotes.
execute myprocedure ('$PROC_DATE');
Previous Topic: New_Form, Call_Form
Next Topic: Highest salary
Goto Forum:
  


Current Time: Fri Mar 29 10:49:25 CDT 2024