Re: Prompting multiples times in PL/SQL??

qazmlp1209_at_rediffmail.com wrote:
> The Table format is as follows:
>
> SQL> desc MyTEST;
> Name Null? Type
> ----------------------------------------- --------
> ----------------------------
> NAME VARCHAR2(10 CHAR)
>
> I want to add 10 rows into this Table, with each row containing the
> 'name' that I want to enter.
> The following PL/SQL script does not work:
>
> ----------
> declare
> i number;
> begin
> for i in 1..10 loop
> insert into tst (name) values ('&name');
> end loop;
> end;
> ----------
>
> It prompts for the 'name' only once and the same name figures in all
> the rows. But, I want to be prompted 10 times and each time I want to
> enter different 'name'. How exactly I should adapt the PL/SQL script
> for this?
>

PL/SQL is not interpreted that way. It's parsed once and pseudo compiled. The &name reference will be expanded/replaced once (actually by sqlplus and not pl/sql) and thats it. There is no "read" or "input" statement for pl/sql. It's not designed to be an interactive language.

Perhaps something like this would work if you are running on unix or have some other korn shell available.

#!/bin/ksh
X=$(tty)
sqlplus -s /nolog > $X 2>&1 |&
print -p connect user/password
((i=0))
while [[ $i -lt 10 ]]
do
  print -n "Enter name: "
  read name
  print -p "insert into tst ('$name');"
  ((i=$i+1))
done
print -p "commit;"
print -p "exit" Received on Thu Jun 22 2006 - 08:59:19 CDT