Home » SQL & PL/SQL » SQL & PL/SQL » Bulk Collect Issue (Oracle 10g, Win XP)
Bulk Collect Issue [message #307016] Mon, 17 March 2008 09:39 Go to next message
freakabhi
Messages: 74
Registered: November 2007
Location: mumbai
Member
Hi all,

I am using bulk collect with Limit clause for
making commit at regular interval....
Now Problem is if there are 3650 records and
commit interval is of 100 then
it will throw error at the last segment of 50 reocrds...

here is my Code:


declare
CURSOR c1
IS Select n_Data_tab_id
from Data_tab
where rownum < 950;

v_fetch_limit NUMBER := 100;
v_cnt number := 0;

TYPE t_n_Data_tab_id IS TABLE OF Data_tab.n_Data_tab_id%TYPE INDEX BY PLS_INTEGER;

n_Data_tab_id t_n_Data_tab_id;

begin
OPEN c1;

LOOP
FETCH c1
BULK COLLECT INTO
n_Data_tab_id
limit v_fetch_limit;


IF n_Data_tab_id.COUNT > 0
THEN

FOR i IN n_Data_tab_id.FIRST .. n_Data_tab_id.LAST
loop

insert into data_tab(id) values(n_Data_tab_id.(i));

end loop;

dbms_output.put('cnt'||n_Data_tab_id.count);

end if;

EXIT WHEN c1%NOTFOUND;
END LOOP;

end;


My findings are at in the last loop track, when only last fifty rows are remained then it throws the error...
EXIT WHEN c1%NOTFOUND;.----

any Ideas on this....
Re: Bulk Collect Issue [message #307017 is a reply to message #307016] Mon, 17 March 2008 09:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Can you tell us what in one of my answers to one of your previous topics don't you understand:
Michel Cadot wrote on Tue, 08 January 2008 10:46
First, please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Always post your Oracle version (4 decimals).

...

Regards
Michel

Re: Bulk Collect Issue [message #307040 is a reply to message #307016] Mon, 17 March 2008 12:15 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Quote:
I am using bulk collect with Limit clause for
making commit at regular interval....
Now Problem is if there are 3650 records and
commit interval is of 100 then


Bad very very bad. I cannot see any commit in your code you have posted. Also, do you fancy getting ora-1555? If so please continue what you are doing. Why don't you try to replace that entire piece of code in one single sql statement and it will be performant. Unless and until you are doing some complex manipulations which you cannot do it in an sql.

Regards

Raj
Re: Bulk Collect Issue [message #307087 is a reply to message #307016] Mon, 17 March 2008 16:20 Go to previous message
coleing
Messages: 213
Registered: February 2008
Senior Member
Hint:

Look up the SAVE EXCEPTIONS clause, and SQL%BULK_EXCEPTIONS and you should find your answer.

Previous Topic: Different result of SELECT COUNT(*)
Next Topic: how to count of record for all tables
Goto Forum:
  


Current Time: Sun Dec 11 06:14:01 CST 2016

Total time taken to generate the page: 0.08886 seconds