Re: Shell parameter passing to stored procedure

Oswaldo Castro wrote:

> 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

Try this:
execute myprocedure ($PROC_DATE) ; # note the ";" forgot it?

or try

# ./run_proc 15032002

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

Inside @exec_procedure I have:

connect user/password_at_inst
spool file_${PROC_DATE}.log # here it works... define procdate=${PROC_DATE}
execute myprocedure (&procdate) ;
exit

rgds
/svend Received on Sun Mar 17 2002 - 02:47:50 CST