I have the following code and it does not work. Does bulk collect can only bind one collection at a time and the below construct is illegal ??
I try to to open a cursor and insert the collection into a parent and a child.
When I comment out the child block it works.
create table parent
(pk int primary key )
/
create table child
(fk constraint child_fk_parent
references parent(pk)
deferrable
initially immediate
)
declare
cursor c1 is select level as kd from dual connect by level<=4;
TYPE C1_TYP IS TABLE OF c1%ROWTYPE;
l_cv_tbl C1_TYP;
begin
OPEN C1;
LOOP
FETCH C1 BULK COLLECT INTO l_cv_tbl limit 10000;
-- dbms_output.put_line('count:'||l_cv_tbl.count);
EXIT WHEN l_cv_tbl.count = 0;
FORALL i IN 1 .. l_cv_tbl.COUNT SAVE EXCEPTIONS
INSERT INTO parent (
pk
)
VALUES (
l_cv_tbl(i).kd
);
INSERT INTO child (
fk
)
VALUES (
l_cv_tbl(i).kd
);
--select * from dual;
--lv_rows_inerted := lv_rows_inerted + SQL%ROWCOUNT;
--COMMIT;
exit when c1%notfound;
END LOOP;
CLOSE C1;
end;