Home » SQL & PL/SQL » SQL & PL/SQL » Help : Problem with Limit clause in BULK COLLECT
Help : Problem with Limit clause in BULK COLLECT [message #270030] Tue, 25 September 2007 08:32 Go to next message
kumarsz
Messages: 180
Registered: March 2005
Location: canada
Senior Member

Hi,

I am using Oracle 9.2.0.6.0 (RAC) on Linux

Please refer folowing code

create or replace procedure test_plsql_prc as 
   v_sql_cursor            VARCHAR2 ( 32000 );
   v_total_rec_count       NUMBER;
   cur_details             sys_refcursor;
   TYPE plsqltab IS TABLE OF VARCHAR2 ( 200 ) INDEX BY BINARY_INTEGER;
   polrectab               plsqltab;
   
  
BEGIN
   v_sql_cursor :='select policynbr from policymaster where rownum<2551';
   
  OPEN cur_details FOR v_sql_cursor;

   LOOP
      FETCH cur_details BULK COLLECT INTO polrectab LIMIT 100;
      EXIT WHEN cur_details%NOTFOUND;
   END LOOP;

   CLOSE cur_details;
   v_total_rec_count          := polrectab.COUNT;

   DBMS_OUTPUT.put_line(to_char(polrectab.COUNT));

END;
/   


I am fetctching 2500 rows into a PL/SQL table using BULK COLLECT with LIMIT cluase

However,
If I use LIMIT clause and Print the PL/SQL table count i am getting the count of records in PL/SQL tables as feteched by last Iteration ONLY.

E.g. for 2550 records, 100 records are fetched 25 times and 50 records are fetched in last iteration while 'LIMIT 100'

Thus i get plsqltable.count = 50 (records fetched in last iteration ONLY)

I tried changing values of LIMIT clause but the rule remains same giving wrong results

And if i remove LIMIT clause then everything works fine.

What could be the Problem?

Thanks and Regards,
Pratap
Re: Help : Problem with Limit clause in BULK COLLECT [message #270035 is a reply to message #270030] Tue, 25 September 2007 08:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post the execution showing what you said.

Regards
Michel
Re: Help : Problem with Limit clause in BULK COLLECT [message #270041 is a reply to message #270035] Tue, 25 September 2007 08:50 Go to previous messageGo to next message
kumarsz
Messages: 180
Registered: March 2005
Location: canada
Senior Member

Michel,

With the Exact code i posted, the execution was as following

SQL> exec test_plsql_prc
50

PL/SQL procedure successfully completed.

SQL>
SQL>


After that i put additional dbms_output as following

 LOOP
      FETCH cur_details BULK COLLECT INTO polrectab LIMIT 100;
	  DBMS_OUTPUT.put_line(polrectab.count || ' rows');
      EXIT WHEN cur_details%NOTFOUND;
   END LOOP;


and this time the execution and results were as followings


SQL>
SQL>
SQL> exec test_plsql_prc
100 rows
100 rows
100 rows
100 rows
100 rows
100 rows
100 rows
100 rows
100 rows
100 rows
100 rows
100 rows
100 rows
100 rows
100 rows
100 rows
100 rows
100 rows
100 rows
100 rows
100 rows
100 rows
100 rows
100 rows
100 rows
50 rows
50

PL/SQL procedure successfully completed.

SQL>

Thanks and Regards,
Pratap
Re: Help : Problem with Limit clause in BULK COLLECT [message #270043 is a reply to message #270041] Tue, 25 September 2007 08:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Well, you ask for 2550 rows and get 25*100+50, what is the problem?

Regards
Michel
Re: Help : Problem with Limit clause in BULK COLLECT [message #270046 is a reply to message #270041] Tue, 25 September 2007 08:58 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
The idea is that you first handle the bundles you fetched. Not first fetch the whole lot and then process.
Re: Help : Problem with Limit clause in BULK COLLECT [message #270050 is a reply to message #270046] Tue, 25 September 2007 09:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, this is the purpose of the LIMIT clause, to limit the amount of memory used and handled the rows pack per pack.
If you have memory enough, then remove the LIMIT clause and handle the whole set.

Regards
Michel
Re: Help : Problem with Limit clause in BULK COLLECT [message #270052 is a reply to message #270030] Tue, 25 September 2007 09:20 Go to previous messageGo to next message
kumarsz
Messages: 180
Registered: March 2005
Location: canada
Senior Member

Frank and Michel

Do do mean i will first fetch all records in PL/SQL table without LIMIT cluase and then for any processing e.g. Insert, Updates, use Limit Clause over PL/SQL table records for optimal memory usage.

However, the way i have used LIMIT isn't it like 'set arraysize' command while fetching records from sqlplus for getting data in chunk?

Thanks and Regards,
Pratap
Re: Help : Problem with Limit clause in BULK COLLECT [message #270071 is a reply to message #270052] Tue, 25 September 2007 10:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes you can say that LIMIT is like ARRAYSIZE in SQL*Plus.
And what does SQL*Plus: it uses a buffer of this size, get the number of rows you specified, DISPLAYS THEM and then get the next pack of rows and so on. It never has the whole set in memory.
You get rows 1 to 100, work on them, then get rows 101 to 200, work on them and so long until the end.

Regards
Michel
Re: Help : Problem with Limit clause in BULK COLLECT [message #270089 is a reply to message #270071] Tue, 25 September 2007 12:29 Go to previous message
kumarsz
Messages: 180
Registered: March 2005
Location: canada
Senior Member

Superb!!

Michel anf Frank, many thanks for the detailed explaination.

Regards,
Pratap

Previous Topic: Help in collections
Next Topic: function
Goto Forum:
  


Current Time: Mon Dec 05 19:04:20 CST 2016

Total time taken to generate the page: 0.14593 seconds