Re: Shell parameter passing to stored procedure

I am using somthing like this:

(file: wrapper)

---
#!/bin/ksh


# generic plsql-procedure wrapper

# Parameters:

# $1 Connect-String eg user/passwd_at_instance

# $2 PLSQL-Procedure


sqlplus -S /nolog << EOF
  CONNECT $1
  set serveroutput on
  EXEC $2
EOF
-----

call it from the shell like
----
string_parameter=hehe
number_paramere=5

wrapper user/passwd_at_instance
"my_plsql_procedure('$string_parameter',$number_parameter)"
---

everything gets substituted ok as far as i know.

hth
christof

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