Re: accessing run-time parameters from PL/SQL block in a .sql file

Originally posted by Arijit

> Hi folks,

> see if u guys can help with any ideas....

>

> I want to write a .sql file to update a table. I know the table

> name(mytable) , column(mycol1) to be updated , only WHERE clause

> condition are supplied from sqlplus.

>

> To update 'n' rows i would call from the command-line :

> sqlplus usr/pw_at_con @my.sql <n> <str-1> <str-2> .....<str-n>

>

> Inside my.sql , i make a pl/sql loop of n times, but do i access the

> parameters index-wise ?

>

> my.sql

> ======

> ...............

>

> declare

> counter number(3,0);

> i number(3,0);

> strname varchar(30);

> .....

> begin

> counter :=&1; /* Access the first parameter telling strings passed

> */

>

> for i in 1..counter loop

> strname := &(i+1) /* Access the i+1 th string */

> update mytable set mycol1=1 where mycol2='strname';

> ..................

> end loop;

>

> end;

> ...

>

>

> This is off course not working. What could be exact syntax ? or do I

> need to use other stuff like Dynamic sql ?

>

> Thanks in anticipation.

> Arijit

To access parameters passed to a script, you MUST use a numeral immediately after an & e.g. &1, &2 etc. You cannot increment it in a loop or use 1+1 for 2 etc.

Either use as many blocks as you have parameters passed to the script and then refer to them as &1 and &2 etc in each block.

OR

Pass one string parameter which has all the parameter strings concatenated separated by a special character e.g. "string1^string2^string3^....". Then tokenize the parameter inside your code. A bit messy though.

Regards

/Rauf Sarwar

--
Posted via http://dbforums.com