Help : Problem with Limit clause in BULK COLLECT [message #270030] |
Tue, 25 September 2007 08:32  |
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 #270041 is a reply to message #270035] |
Tue, 25 September 2007 08:50   |
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 #270052 is a reply to message #270030] |
Tue, 25 September 2007 09:20   |
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
|
|
|
|
|