Home » SQL & PL/SQL » SQL & PL/SQL » example of returning clause with insert needed
example of returning clause with insert needed [message #18918] Thu, 21 February 2002 05:09 Go to next message
ksr
Messages: 112
Registered: January 2002
Senior Member
Hi,
i want something like this..please note the syntay may not be correct..

i have a sequence and a trigger on insert over the table "test".
When a new record is inserted,the trigger fires and for the test.col1 value is inserted.

declare
cursor c1 is select col2,col3 from table1;
arr_number varray;(dont know the size to declare)
id number:=10;
begin
for cur1 in c1 loop
insert into test values (cu1.col2,cur1.col3) returning col1 into arr_number;
end loop;
--i want to append the values returned by the above --insert statement(adding into the array) and then

loop through the array
insert into table3 values(id,arr_number(i));
end loop;

end loop;
end;

I want something like that..can anyone give me the correct syntax,i have tried some things but it does not work.
Re: example of returning clause with insert needed [message #18922 is a reply to message #18918] Thu, 21 February 2002 05:50 Go to previous message
Suresh Vemulapalli
Messages: 624
Registered: August 2000
Senior Member
declare
cursor c1 is select col2,col3 from table1;
type tab1 is table of number index by binary_integer;

arr_number tab1;
id number:=10;
ctr number:=0;
begin
for cur1 in c1 loop
ctr := ctr+1;
insert into test values (cu1.col2,cur1.col3) returning col1 into arr_number(ctr);
end loop;

for i in 1..arr_number.count loop
insert into table3 values(id,arr_number(i));
end loop;

end;
Previous Topic: very urgent...how to do looping inside a table
Next Topic: how to get list of all child tables for a particular parent table
Goto Forum:
  


Current Time: Fri Sep 24 07:51:31 CDT 2021