| Bulk process with limit advice [message #251314] |
Thu, 12 July 2007 19:54  |
tcy531
Messages: 1
Registered: July 2007
|
Junior Member |
|
|
Here my sample design of the bulk process with limit of row inserted
declare
n_rows_to_process NUMBER := 1000;
CURSOR my_cursor IS
select XXXXX
BEGIN
OPEN my_cursor;
loop
FETCH my_cursor BULK COLLECT INTO
XXXX
LIMIT n_rows_to_process
FORALL i IN 1 .. PUR_CCN_TAB.COUNT
insert XXXX
commit;
--
-- return;
exit when my_cursor%NOTFOUND;
--
END LOOP my_cursor_loop;
CLOSE my_cursor;
End;
This is only for 1 cursor select. It complete inserted the row into temp table.
So , when I put 2 cursor into this process. It wouldn't run the second cursor because the 1 cursor it already exit the process.
Here my sample design for 2 cursor
declare
n_rows_to_process NUMBER := 1000;
CURSOR my_cursor IS
select XXXXX
CURSOR my_cursor2 IS
select XXXXX
BEGIN
OPEN my_cursor;
loop
FETCH my_cursor BULK COLLECT INTO
XXXX
LIMIT n_rows_to_process
FORALL i IN 1 .. PUR_CCN_TAB.COUNT
insert XXXX
commit;
--
-- return;
exit when my_cursor%NOTFOUND;
--
END LOOP my_cursor_loop;
CLOSE my_cursor;
BEGIN
OPEN my_cursor2;
loop
FETCH my_cursor2 BULK COLLECT INTO
XXXX
LIMIT n_rows_to_process
FORALL i IN 1 .. PUR_CCN_TAB.COUNT
insert XXXX
commit;
--
-- return;
exit when my_cursor2%NOTFOUND;
--
END LOOP my_cursor2_loop;
CLOSE my_cursor2;
End;
Is there any SQL command we can used like complete the 1 loop and continue the 2nd loop?
the reason i need the loop process because i want to insert the row by limit.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
