BULK COLLECT INTO with limit? [message #348856] |
Thu, 18 September 2008 02:37  |
gmoth
Messages: 17 Registered: September 2008 Location: South Africa
|
Junior Member |
|
|
The way I understood bulk collect with a limit was that you set a limit and once you hit that limit you can populate that data into a collection or whatever and then you can re-open that cursor and then go fetch the next rows.
So if i do bulk collect and i set limit of 5 rows and the table holds 10 I thought it would retrieve first 5 then go back and retrieve the rest. From what I am seeing it only retrieves 5 and thats it, it hits its limit and thats it.
See below this is a very simple example i wrote just to test bulk. I want to retrieve x amount of recs then populate a table then go back and get the next records isnt this what limit is for?
CREATE OR REPLACE PROCEDURE fc_gm_test_01
IS
CURSOR clarify_csr
IS
SELECT *
FROM fc_clarify_fnf
WHERE ROWNUM < 11;
TYPE clarify_type IS TABLE OF fc_clarify_fnf%ROWTYPE;
clarify clarify_type;
v_rows NUMBER;
BEGIN
v_rows := 5;
OPEN clarify_csr;
FETCH clarify_csr
BULK COLLECT INTO clarify LIMIT v_rows;
CLOSE clarify_csr;
FOR i IN 1 .. clarify.COUNT
LOOP
DBMS_OUTPUT.put_line ( clarify (i).x_id_red
|| ' '
|| clarify (i).fnf_number
);
END LOOP;
END fc_gm_test_01;
/
|
|
|
|
Re: BULK COLLECT INTO with limit? [message #348858 is a reply to message #348856] |
Thu, 18 September 2008 02:41   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
What you describe is not what your code is doing - you make no attempt to go back and fetch a second set of rows from the cursor.
Bulk Collect simply means that you fetch the rows from the cursor in sets rather than individually - if you have not finished with the cursor, don't close it - just leave it and fetch from it again when you have processed the current set of rows.
Additionally, in Oracle 10g, you only need to use this in special circumstances, as all cursor for loops do a Bulk Collect Limit 100 behind the scenes.
|
|
|
|
|
|
Re: BULK COLLECT INTO with limit? [message #348902 is a reply to message #348890] |
Thu, 18 September 2008 04:31   |
gmoth
Messages: 17 Registered: September 2008 Location: South Africa
|
Junior Member |
|
|
Guys one last thing.
Everything works fine but with the example above I pull 10 records so 10 records should be displayed. If i set the limit to 5 it pulls 5 and then another 5. Now the wierd thing is if i set the limit to 3 it only pulls 9 and does not recognize the last row.
Why is this?
Does bulk collect need more than one record to collect? If so how do we cater for the one record?
Is it the for loop that is the problem?
Please assist.
|
|
|
|
Re: BULK COLLECT INTO with limit? [message #348909 is a reply to message #348905] |
Thu, 18 September 2008 04:50   |
gmoth
Messages: 17 Registered: September 2008 Location: South Africa
|
Junior Member |
|
|
I found the problem you see i normally open a cursor and the fetch into the put a EXIT WHEN %NOTFOUND.
What i have done now is put the EXIT WHEN %NOTFOUND on the cursor after i populate my table.
So it was like this...
OPEN clarify_csr;
LOOP
FETCH clarify_csr
BULK COLLECT INTO clarify LIMIT v_rows;
EXIT WHEN clarify_csr%NOTFOUND;
FOR i IN 1 .. clarify.COUNT
LOOP
fc_gm_test_01_pkg.myprocedure (clarify (i).x_id_red,
clarify (i).fnf_number
);
END LOOP;
DBMS_OUTPUT.put_line ('Howziiiiiiiiiit my bru!!!');
END LOOP;
Now its like this...
OPEN clarify_csr;
LOOP
FETCH clarify_csr
BULK COLLECT INTO clarify LIMIT v_rows;
FOR i IN 1 .. clarify.COUNT
LOOP
fc_gm_test_01_pkg.myprocedure (clarify (i).x_id_red,
clarify (i).fnf_number
);
END LOOP;
EXIT WHEN clarify_csr%NOTFOUND;
DBMS_OUTPUT.put_line ('Howziiiiiiiiiit my bru!!!');
END LOOP;
The wierd thing is I wouldn't have thought this would make difference as there was still one record left when the last open took place so I am not sure why by putting the %NOT FOUND after populating the table now works.
I tried formatter and it just gives me errors but i ran this my side and it complies perfectly. I am sorry in advance.
|
|
|
|
Re: BULK COLLECT INTO with limit? [message #348953 is a reply to message #348937] |
Thu, 18 September 2008 06:43   |
gmoth
Messages: 17 Registered: September 2008 Location: South Africa
|
Junior Member |
|
|
Hi Michel
Ok i did some research on the net and found that %NOTFOUND will not work as that will only come into effect if amount returned is not same size as limit. I read on another forum that its something not lot of people know so i used EXIT WHEN clarify.COUNT = 0;
My next problem is I need to store all values from that bulk collect into that table and only once ALL data is collected do i needto write to file. The reason for this is if there is a failure while collecting nothing must go to a file.
So i decided to take my loop i.e FOR i IN 1 .. clarify.COUNT outside of the loop that bulk collects and after i have closed it to then loop through the values and send but for some reason it does nothing.
Do you have to use the values within the bulk loop why cant you use them after?
|
|
|
|
Re: BULK COLLECT INTO with limit? [message #349162 is a reply to message #348958] |
Fri, 19 September 2008 02:55   |
gmoth
Messages: 17 Registered: September 2008 Location: South Africa
|
Junior Member |
|
|
I read that article its interesting but I something still doesn't make sense.
Okay so I can populate a table without the limit clause and access it after i closed the cursor but if i add a limit with the loop around it I cannot access the table values after i closed the cursor. Why is this?
My scenario requires this is it a question of PL/SQL just not catering for it?
I need to collect a lot of data I need a limit so that I do not collect too much at once and only once all data is populated do I need to write to a file so why can I not do this? Whyu can i not access table values after closing the cursor if i use a limit? Without a limit I can but thats doesn't make sense.
|
|
|
|
Re: BULK COLLECT INTO with limit? [message #349177 is a reply to message #349162] |
Fri, 19 September 2008 03:34   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
You seem to be getting quite confused about things.
Let's see if we can straighten a few things out.
1) You can access the table after you close the cursor - it is simple a Pl/Sql collection.
What is happening is this: Each time you do a Fetch into your table CLARIFY, it overwrites the previous rows that were in the table, in exactly the same wat that fatching a single value into a variable would overwrite that variable.
You are exiting the loop after your fetch returns 0 rows, so obviously your table has no rows in it at that point.
If it is your goal to fetch all the data into a collection at once, and then work through the collection, there is nothing to be gained from using the LIMIT clause - it is there to allow you to limit the size of a collection and keep memory requirements to a minimum.
2) You can use SQL%NOTFOUND to indicate when to exit the loop.
SQL%NOTFOUND is set when you perform a Fetch, and it returns less than the Limit number of rows. If you exit the loop at this point, then you wil have fetched all the rows.
Here's an example you can run to show what I mean. Try running this with a LIMIT of 30 and 25, adn you'll see when %NOTFOUND gets set:create table test_0103 (col_1 number, col_2 varchar2(100));
insert into test_0103 select level, 'Row '||level from dual connect by level <= 100;
declare
type ty_tab is table of test_0103%rowtype index by binary_integer;
t_fetch ty_tab;
v_idx pls_integer := 0;
cursor c_1 is
select *
from test_0103;
begin
open c_1;
loop
fetch c_1 bulk collect into t_fetch limit 30;
v_idx := v_idx +1;
dbms_output.put_line('Loop '||v_idx||' Rows '||t_fetch.count);
if c_1%notfound then
dbms_output.put_line('C_1%NOTFOUND');
end if;
if c_1%found then
dbms_output.put_line('C_1%FOUND');
end if;
if v_idx = 5 then
exit;
end if;
end loop;
close c_1;
end;
/
|
|
|
Re: BULK COLLECT INTO with limit? [message #349186 is a reply to message #349177] |
Fri, 19 September 2008 04:02   |
gmoth
Messages: 17 Registered: September 2008 Location: South Africa
|
Junior Member |
|
|
Thanks a lot makes perfect sense I guess my worry was say there was a huge amount of records i thought it would be safer to bulk collect a certain amount at a time but realistically it makes no difference.
If you do use limit generally you would access each value within the cursor and you would limit it for memory reasons.
|
|
|
Re: BULK COLLECT INTO with limit? [message #349217 is a reply to message #349186] |
Fri, 19 September 2008 06:16   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Quote: | If you do use limit generally you would access each value within the cursor and you would limit it for memory reasons.
|
Yes. BULK COLLECT is usualy used as a way of speeding up processing that has to be done row-by-slow-row. It lets you speed up the retrieval of data from the Db.
In 10g, all cursor for loops perform a Limit 100 fetch in the background as an enhancement to the way pl/sql handles cursors
|
|
|
Re: BULK COLLECT INTO with limit? [message #349225 is a reply to message #349217] |
Fri, 19 September 2008 06:42   |
gmoth
Messages: 17 Registered: September 2008 Location: South Africa
|
Junior Member |
|
|
Thanks a lot makes perfect sense.
So basically in my case whether i use a plain cursor on its own or a bulk collect it will make no difference really?
Should i stick with a plain cursor or just throw bulk collect in?
Thanks again...
|
|
|
Re: BULK COLLECT INTO with limit? [message #349226 is a reply to message #349225] |
Fri, 19 September 2008 06:43   |
gmoth
Messages: 17 Registered: September 2008 Location: South Africa
|
Junior Member |
|
|
By the way we are using 9i so i guess in 9i case bulk collect could make big diff but if we moved to 10g a cursor on its own vs bulk collect will make little difference?
|
|
|
|
Re: BULK COLLECT INTO with limit? [message #349523 is a reply to message #349235] |
Mon, 22 September 2008 02:33   |
gmoth
Messages: 17 Registered: September 2008 Location: South Africa
|
Junior Member |
|
|
Thanks again...
So what do you suggest as I need to collect all data make sure its perfect before writing that whole collection of data to a file. Thats why I though first collect it into a collection if it all is there only then pass the collection to a function to write to a file using UTL.
|
|
|
|