Home » Other » Client Tools » Interactive sql script
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
Previous Topic: how does non english characters get store in oracle
Next Topic: features of oracle8i, 9i and 11i
Goto Forum:
  


Current Time: Thu Mar 28 09:09:27 CDT 2024