Re: Passing UNIX shell variables to a stored procedure

"Anurag Varma" <avdbi_at_hotmail.com> wrote in message news:MIWpb.27$um1.25_at_news02.roc.ny...
>
> "Brad" <brsimmon_at_mobility.com> wrote in message news:7fba17f9.0311041406.1a50a0e7_at_posting.google.com...
> --snip--
> > 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.
>
> You are trying to pass in a variable to sqlplus? Not sure I understand that.
> Maybe Hans guessed correctly what you plan to do.
>
> For me, I'd like to see what exactly you plan on passing (i.e. what is $1 equal to?).
>
> Also note: sqlplus -s will suppress the banner which you are painfully trying to grep out.
>
> sqlplus -s user/pass_at_db
>
> Anurag
>
>

.. BTW: are you trying to do something like this?

cat test.ksh
#!/bin/ksh

print "
connect user/pass_at_db

var v varchar2(100)

exec :v := substr('$1',1,3);

print v

exit;
" | sqlplus -s /nolog | grep -v 'Connected'

Runing the shell script...
[avarma] test.ksh first

PL/SQL procedure successfully completed.

V

Anurag Received on Tue Nov 04 2003 - 18:07:31 CST