Home » SQL & PL/SQL » SQL & PL/SQL » BULK COLLECT INTO with limit?
BULK COLLECT INTO with limit? [message #348856] Thu, 18 September 2008 02:37 Go to next message
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 #348857 is a reply to message #348856] Thu, 18 September 2008 02:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

You have to loop on select to get the next 5 rows.

Regards
Michel
Re: BULK COLLECT INTO with limit? [message #348858 is a reply to message #348856] Thu, 18 September 2008 02:41 Go to previous messageGo to next message
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 #348860 is a reply to message #348857] Thu, 18 September 2008 02:48 Go to previous messageGo to next message
gmoth
Messages: 17
Registered: September 2008
Location: South Africa
Junior Member
Sorry about that it wont happen again Smile

So put a loop before I open the cursor then after populating the array right at the end? Do i close after each fetch or only at the end.

A code example would be HIGHLY appreciated.
Re: BULK COLLECT INTO with limit? [message #348865 is a reply to message #348860] Thu, 18 September 2008 03:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
open
loop
  fetch limit
  loop on result table
  ...
  end loop
end loop
close

Regards
Michel

[Updated on: Thu, 18 September 2008 03:04]

Report message to a moderator

Re: BULK COLLECT INTO with limit? [message #348890 is a reply to message #348865] Thu, 18 September 2008 04:08 Go to previous messageGo to next message
gmoth
Messages: 17
Registered: September 2008
Location: South Africa
Junior Member
Thanks a lot guys much appreciated.
Re: BULK COLLECT INTO with limit? [message #348902 is a reply to message #348890] Thu, 18 September 2008 04:31 Go to previous messageGo to next message
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 #348905 is a reply to message #348902] Thu, 18 September 2008 04:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Copy and paste you code and session (format it).

Regards
Michel
Re: BULK COLLECT INTO with limit? [message #348909 is a reply to message #348905] Thu, 18 September 2008 04:50 Go to previous messageGo to next message
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 #348937 is a reply to message #348909] Thu, 18 September 2008 05:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
So it was like this...

No.
Read again CAREFULLY:
Michel Cadot wrote on Thu, 18 September 2008 11:42
Copy and paste your code and session (format it).

Regards
Michel



Re: BULK COLLECT INTO with limit? [message #348953 is a reply to message #348937] Thu, 18 September 2008 06:43 Go to previous messageGo to next message
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 #348958 is a reply to message #348953] Thu, 18 September 2008 06:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
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 don't use LIMIT!

Quote:
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;

Oracle Magazine, Steven Feuerstein, On BULK COLLECT

Regards
Michel
Re: BULK COLLECT INTO with limit? [message #349162 is a reply to message #348958] Fri, 19 September 2008 02:55 Go to previous messageGo to next message
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 #349174 is a reply to message #349162] Fri, 19 September 2008 03:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
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 I need to write to a file so why can I not do this?

What is the difference between:
* Fetch all at once
* Fetching bit per bit, storing all before handling
Answer: none, in both case you have to store all the data.

Regards
Michel
Re: BULK COLLECT INTO with limit? [message #349177 is a reply to message #349162] Fri, 19 September 2008 03:34 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #349235 is a reply to message #349225] Fri, 19 September 2008 07:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Should i stick with a plain cursor or just throw bulk collect in?

As you want to be sure to fetch all data before writing to file then you should use a bulk collect without limit.

By the way, having all the data in a memory table does not mean you will be able to write the full stuff before something (bad) happens.

Regards
Michel
Re: BULK COLLECT INTO with limit? [message #349523 is a reply to message #349235] Mon, 22 September 2008 02:33 Go to previous messageGo to next message
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.
Re: BULK COLLECT INTO with limit? [message #349538 is a reply to message #349523] Mon, 22 September 2008 02:52 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I suggest to use "spool" and select and get rid of PL/SQL but I don't know your business need.

Regards
Michel

[Updated on: Mon, 22 September 2008 02:52]

Report message to a moderator

Previous Topic: Date list between two dates without base table
Next Topic: Warning while creating procedure
Goto Forum:
  


Current Time: Sat Dec 03 13:46:42 CST 2016

Total time taken to generate the page: 0.26660 seconds