Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Shell parameter passing to stored procedure

Re: Shell parameter passing to stored procedure

From: Joe Kazimierczyk <kazimiej_at_bms.com>
Date: 18 Mar 2002 05:31:54 -0800
Message-ID: <e2242da1.0203180531.33c8f401@posting.google.com>


ogcastro_at_zaz.com.br (Oswaldo Castro) wrote in message news:<b3fee624.0203161114.7461ad14_at_posting.google.com>...
> 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.

Inside of @exec_procedure.sql, it's pure SQL, so it won't know about your shell variables. You need to let bash do the substitution, then pass the values to the sql script, something like:

...
su - oracle -c "PROC_DATE=$PROC_DATE sqlplus /nolog @exec_procedure.sql $PROC_DATE"

then exec_procedure.sql has the date as sqlplus's arg1:

execute myprocedure ('&1');
-- or maybe use to_date('&1','datefmt') if appropriate Received on Mon Mar 18 2002 - 07:31:54 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US