Re: Passing Unix Params to a Stored Procedure

From: FC <flavio_at_tin.it>
Date: Mon, 06 Jan 2003 14:32:58 GMT
Message-ID: <usgS9.65044$Q%4.2066340@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
>
>
Received on Mon Jan 06 2003 - 08:32:58 CST