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

[Quoted] I want to write a .sql file to update a table. I know the table [Quoted] name(mytable) , column(mycol1) to be updated , only WHERE clause [Quoted] condition are supplied from sqlplus.

[Quoted] To update 'n' rows i would call from the command-line : [Quoted] sqlplus usr/pw_at_con @my.sql <n> <str-1> <str-2> .....<str-n>

[Quoted] Inside my.sql , i make a pl/sql loop of n times, but do i access the [Quoted] parameters index-wise ?

my.sql

declare
[Quoted] counter number(3,0);
[Quoted] i number(3,0);
[Quoted] strname varchar(30);
.....
begin
[Quoted]   counter :=&1; /* Access the first parameter telling strings passed */   

              for i in 1..counter loop
[Quoted]               strname := &(i+1)  /* Access the i+1 th string */
[Quoted]               update mytable set mycol1=1 where mycol2='strname';
 ..................
          end loop;

end;
...

[Quoted] This is off course not working. What could be exact syntax ? or do I [Quoted] need to use other stuff like Dynamic sql ?

Thanks in anticipation.
Arijit Received on Wed Aug 27 2003 - 16:00:24 CEST