Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Passing Unix Params to a Stored Procedure
"FC" <flavio_at_tin.it> wrote in message
news:usgS9.65044$Q%4.2066340_at_news1.tin.it...
> According to Oracle's documentation you can pass positional arguments when
> invoking an external script using the command START. Command "@" should
work
> as well.
>
>
> Excepted from SQL Plus Guide:
> ----------------------------------------------
> ...
> You can bypass the prompts for values associated with substitution
variables
> by passing values to parameters in a command file through the START
command.
>
> You do this by placing an ampersand (&) followed by a numeral in the
command
> file in place of a substitution variable. Each time you run this command
> file, START replaces each &1 in the file with the first value (called an
> argument) after START filename, then replaces each &2 with the second
value,
> and so forth.
>
>
> For example, you could include the following commands in a command file
> called MYFILE:
>
> SELECT * FROM EMP
> WHERE JOB='&1'
> AND SAL=&2In the following START command, SQL*Plus would substitute CLERK
> for &1 and 7900 for &2 in the command file MYFILE:
>
> SQL> START MYFILE CLERK 7900When you use arguments with the START command,
> SQL*Plus DEFINEs each parameter in the command file with the value of the
> appropriate argument.
>
> ....
>
> -------------------------------------------------------------------------
>
>
>
>
> "Roy Martin" <rmartin_at_bfsec.bt.co.uk> wrote in message
> news:avc19d$623$1_at_pheidippides.axion.bt.co.uk...
> > need to pass Unix parameters to a stored procedure (called in proc.sql)
> >
> > I call proc.sql like so (I've ommited the sql logon info etc):
> >
> > @proc.sql $1 $2 $3
> >
> > Within proc.sql I call the stored procedure:
> >
> > my_proc('&1', '&2', '&3');
> >
> > However this doesn't pick up the parameters passed from the Unix script.
> Am
> > I correct in thinking that &1 etc is the correct way to bind parameters?
> > Whenever I run proc.sql from the sql prompt, I get prompted to enter the
> > three values and the script executes correctly.
> >
> > TIA
> >
> >
>
>
Just what I needed, thanks!
Received on Mon Jan 06 2003 - 08:37:31 CST