returning collection from a loop and bulk collect [message #666054] |
Tue, 10 October 2017 11:06 |
|
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 #666057 is a reply to message #666054] |
Tue, 10 October 2017 11:47 |
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 #666063 is a reply to message #666062] |
Tue, 10 October 2017 14:55 |
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.
|
|
|
|