Home » SQL & PL/SQL » SQL & PL/SQL » returning collection from a loop and bulk collect (Oracle 11)
returning collection from a loop and bulk collect [message #666054] Tue, 10 October 2017 11:06 Go to next message
roger6701
Messages: 6
Registered: October 2017
Junior Member
Hi

I need to return collection ( my_collection ) from the following loop. How do I do this ? There are about 10000 records and I am looping with limit 250. Is there a way I can return this in 250 count ?

BEGIN
OPEN RC1;
LOOP
FETCH RC1 BULK COLLECT INTO my_collection limit 250;
EXIT when my_collection.COUNT = 0;
END LOOP ;
CLOSE RC1;
END;

thanks for the help !!
Roger
Re: returning collection from a loop and bulk collect [message #666055 is a reply to message #666054] Tue, 10 October 2017 11:30 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

Do NOT do in PL/SQL that which can be done in plain SQL.

Return to where exactly? (& why?)
Re: returning collection from a loop and bulk collect [message #666056 is a reply to message #666055] Tue, 10 October 2017 11:44 Go to previous messageGo to next message
roger6701
Messages: 6
Registered: October 2017
Junior Member
Thanks for looking into this ...

I used to use a stored proc and pkg to return results to the client

create or replace PACKAGE pkg01
is
type yp1 is record
(
A,
B
)
TYPE pqj1 IS REF CURSOR RETURN yp1;
END;

create or replace PROCEDURE send_records
(
RC1 in out pkg01.pqj1
)
is
open RC1 for
select a, b, from xyz;

From above I used to read them one by one ..but now need is to use bulk collect and return them.

Let me know...
Re: returning collection from a loop and bulk collect [message #666057 is a reply to message #666054] Tue, 10 October 2017 11:47 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Not entirely sure what you mean by "in 250 count". Each FETCH RC1 BULK COLLECT INTO my_collection limit 250 will fetch 250 rows (or less if it is last incomplete batch) overwriting whatever was in my_collection before. So code you posted is useless since my_collection is empty after the loop.

SY.
Re: returning collection from a loop and bulk collect [message #666058 is a reply to message #666057] Tue, 10 October 2017 12:09 Go to previous messageGo to next message
roger6701
Messages: 6
Registered: October 2017
Junior Member
I need to return those 250 records if it is possible. Or collect them and return. I have posted my earlier flow which use pkg and proc. Let me know how I can handle this to return the collection.

thanks...
Re: returning collection from a loop and bulk collect [message #666059 is a reply to message #666058] Tue, 10 October 2017 12:37 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Do you mean you want caller to get 250 rows at a time?

SY.
Re: returning collection from a loop and bulk collect [message #666060 is a reply to message #666059] Tue, 10 October 2017 12:44 Go to previous messageGo to next message
roger6701
Messages: 6
Registered: October 2017
Junior Member
Yes. If that is not possible how can I collect and send all of them ( ~10000 ) .

thanks !!
Re: returning collection from a loop and bulk collect [message #666061 is a reply to message #666060] Tue, 10 October 2017 12:50 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
If you want to collect all of them then use BULK COLLECT without LIMIT.

SY.
Re: returning collection from a loop and bulk collect [message #666062 is a reply to message #666061] Tue, 10 October 2017 13:00 Go to previous messageGo to next message
roger6701
Messages: 6
Registered: October 2017
Junior Member
So there is no way we can return records in a set instead of all of them ?

Also can you give me a hint about how to return the full collection ? I used to read 1 record at a time as I shown in my code above ..now I am using "bulk collect".

thanks !!
Re: returning collection from a loop and bulk collect [message #666063 is a reply to message #666062] Tue, 10 October 2017 14:55 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
SQL> create or replace
  2    package pkg01
  3    is
  4      type rec_type
  5        is record(
  6                  a varchar2(20),
  7                  b varchar2(20)
  8                 );
  9      type rec_tbl_type
 10        is table of rec_type;
 11      type ref_cur_type
 12        is ref cursor
 13           return rec_type;
 14  end;
 15  /

Package created.

SQL> create or replace
  2    procedure send_records(
  3                           p_cur        in out pkg01.ref_cur_type,
  4                           p_flag       in out number,
  5                           p_limit      in     number,
  6                           p_collection    out pkg01.rec_tbl_type
  7                          )
  8      is
  9      begin
 10          if p_flag = 1
 11            then
 12              open p_cur
 13                for select  ename,
 14                            job
 15                      from  emp;
 16              p_flag := 0;
 17          end if;
 18          fetch p_cur
 19            bulk collect
 20              into p_collection
 21              limit p_limit;
 22  end;
 23  /

Procedure created.

SQL> declare
  2      v_cur        pkg01.ref_cur_type;
  3      v_collection pkg01.rec_tbl_type;
  4      v_flag       number := 1;
  5      v_limit      number := 7;
  6      v_n          number := 1;
  7  begin
  8      loop
  9        send_records(
 10                     v_cur,
 11                     v_flag,
 12                     v_limit,
 13                     v_collection
 14                    );
 15        for v_i in 1..v_collection.count loop
 16          if v_i = 1
 17            then
 18              dbms_output.put_line('Fetch ' || v_n);
 19              dbms_output.put_line('-------');
 20          end if;
 21          dbms_output.put_line(rpad(v_collection(v_i).a,21) || v_collection(v_i).b);
 22        end loop;
 23        exit when v_collection.count < v_limit;
 24        v_n := v_n + 1;
 25      end loop;
 26      close v_cur;
 27  end;
 28  /
Fetch 1
-------
SMITH                CLERK
ALLEN                SALESMAN
WARD                 SALESMAN
JONES                MANAGER
MARTIN               SALESMAN
BLAKE                MANAGER
CLARK                MANAGER
Fetch 2
-------
SCOTT                ANALYST
KING                 PRESIDENT
TURNER               SALESMAN
ADAMS                CLERK
JAMES                CLERK
FORD                 ANALYST
MILLER               CLERK

PL/SQL procedure successfully completed.

SQL> 

SY.
Re: returning collection from a loop and bulk collect [message #666064 is a reply to message #666063] Tue, 10 October 2017 16:00 Go to previous message
roger6701
Messages: 6
Registered: October 2017
Junior Member
Hi Solomon,

Wonderful demonstration. Works good for me and also learnt new things from it.

Thank very much ...appreciate it !!

Roger !!
Previous Topic: Heterogeneous DB error propagating past WHEN OTHERS
Next Topic: Combination of more/less and brackets cause TRUE or FALSE (merged 2)
Goto Forum:
  


Current Time: Thu Mar 28 18:50:52 CDT 2024