Home » SQL & PL/SQL » SQL & PL/SQL » Bulk process with limit advice
Bulk process with limit advice [message #251314] Thu, 12 July 2007 19:54 Go to next message
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.
Re: Bulk process with limit advice [message #251338 is a reply to message #251314] Fri, 13 July 2007 01:16 Go to previous message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Please read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS
Make sure that lines of code do not exceed 80 or 100 characters when you format.
Please always post your Oracle version (4 decimals).

Non formatted code is unreadable.

Regards
Michel
Previous Topic: need query help
Next Topic: Old ANSI join syntax works for correlated subquery, new ANSI join syntax does not --- why?
Goto Forum:
  


Current Time: Fri Dec 06 01:39:22 CST 2024