| Bulk Collect Issue [message #307016] |
Mon, 17 March 2008 09:39  |
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 #307040 is a reply to message #307016] |
Mon, 17 March 2008 12:15  |
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
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
