| example of returning clause with insert needed [message #18918] |
Thu, 21 February 2002 05:09  |
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  |
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;
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
