Re: Passing UNIX shell variables to a stored procedure

"Anurag Varma" <avarmadba.skipthis_at_yahoo.com> wrote in message news:<CsBpb.32159$Q9.13430_at_nwrddc02.gnilink.net>...
> "Brad" <brsimmon_at_mobility.com> wrote in message
> news:7fba17f9.0311031335.7cfc54e2_at_posting.google.com...
> > Hello,
> >
> > I have a few stored procedures in Oracle 8i and am interested in
> > passing UNIX shell variables to some of them upon execution. Is that
> > possible and if so how do I go about it?
> >
> > Thanks in advance,
> > Brad.
>
> Try something like this (beware: not tested!)
>
> myscript.ksh
>
> #!/bin/ksh
>
> sqlplus /nolog <<EOT
> connect user/pass
>
> @myscript.sql $1 $2
>
> exit;
>
> ...
>
> And then refer to the variables in your sql script like this:
>
> myscript.sql
>
> exec myproc('&1','&2');
>
> ..... assuming 1 & 2 are characters ... change accordingly to your specs...
>
> Anurag

Thanks very much for the response Anurag, I read it and though it would work, Eurika! but it doesn't. In your example you are passing in variables from the sqlplus prompt, I am trying to pass in variables from UNIX. This will be executed by cron from unix, it loads sqlplus through the script. This is the actual code I am calling:

sqlplus user${dbName}/user${dbName} < $1 | grep -v Partitioning |\
                                           grep -v SQL |\
                                           grep -v JServer |\
                                           grep -v Oracle |\
                                           grep -v Connected |\
                                           grep -v "^$" |\
                                           grep -v "^-$" |\
                                           grep -v Copyright |\
                                           grep -v Release |\
                                           grep -v deleted |\
                                           grep -v Commit

where $1 is the sqlplus I am executing. I need to pass to $1 in this example a variable. I have tried any number of '' or "" around the $1 and the variable I am trying to pass, but sqlplus thinks its part of the file name and fails.

Brad. Received on Tue Nov 04 2003 - 16:06:49 CST