| Interactive sql script [message #25917] |
Mon, 26 May 2003 09:59 |
Rajesh Joshi
Messages: 12
Registered: July 2002
|
Junior Member |
|
|
Hi ,
I want to create an interactive sql script which will accept value from user and use it to perform an update on a table.
However i want to use this inside a pl/sql procedure.
this is beacause i want user to see old data from database and ask him to enter new values for it.
problem is that when i am using dbms_output.put_line for displaying the old data and using substitution variable for accepting data from user, the program
prompts for user input BEFORE displaying the dbms_output.put_line message.
This negates the whole exercise .
my code is below
declare
v varchar2(35);
cursor c1 is select cust_no from exceptions;
begin
for c1rec in c1
loop
select bad_street into v from exceptions
where cust_no=c1rec.cust_no;
dbms_output.put_line ('Old street is '|| v);
update test1
set st_code=&n1,
st_name=&n2
where cust_no=c1rec.cust_no;
end loop;
end;
The cursor fetches 3 recs from database.
The output i get is
Enter value for n1: 'as'
Enter value for n2: 'asss'
Old street is abcd
Old street is basdcd
Old street is aasdbcd
I want the user to be prompted for each of the 3 records for his input.
Can anyone suggest me a way for doing that.
Thanks
RAJESH
Thanks in advance
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
