Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> returning collection results from loop and bulk collect
Roger_67
post Oct 10 2017, 11:20 AM
Post #1


Newbie
*

Group: Members
Posts: 3
Joined: 10-October 17
Member No.: 52,200



Hi

I am trying to return collection results from a loop which does bulk collection with limit option. There are about 10000 records and I am lopping with 250 limit. Is there a way I can return results each time I fetch .. that is 250 records each time ? if not how do I collect all of them and return ?

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 your help !!

Roger
Go to the top of the page
 
+Quote Post
burleson
post Oct 10 2017, 04:12 PM
Post #2


Advanced Member
***

Group: Members
Posts: 13,577
Joined: 26-January 04
Member No.: 13



Hi Roger, and welcome to the forum!

I am asking a PL/SQL guru to pop-in and address your question.

In the meantime, here are my notes on the limit clause of bulk collect:

http://www.dba-oracle.com/plsql/t_plsql_limit_clause.htm


--------------------
Hope this helps . . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
boobal_ganesan
post Oct 10 2017, 04:24 PM
Post #3


Advanced Member
***

Group: Members
Posts: 85
Joined: 6-June 16
From: India
Member No.: 51,370



QUOTE (Roger_67 @ Oct 10 2017, 10:50 PM) *
Hi

I am trying to return collection results from a loop which does bulk collection with limit option. There are about 10000 records and I am lopping with 250 limit. Is there a way I can return results each time I fetch .. that is 250 records each time ? if not how do I collect all of them and return ?

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 your help !!

Roger



Hello Roger,

In your case, you can directly return your REF CURSOR instead of copying its data into a collection and then to return the collection.

On either case, you have to create your code as a part of a procedure or a function and have its OUT parameter assigned to the REF CURSOR or the COLLECTION, so that you can collect them at your output parameter and use it for your further processing.


Thank you,
Boobal Ganesan


--------------------
Check out my new Advanced PL/SQL book here - http://www.rampant-books.com/book_1701_pls...initive_ref.htm
Go to the top of the page
 
+Quote Post
Roger_67
post Oct 10 2017, 05:11 PM
Post #4


Newbie
*

Group: Members
Posts: 3
Joined: 10-October 17
Member No.: 52,200



QUOTE (boobal_ganesan @ Oct 10 2017, 04:24 PM) *
Hello Roger,

In your case, you can directly return your REF CURSOR instead of copying its data into a collection and then to return the collection.

On either case, you have to create your code as a part of a procedure or a function and have its OUT parameter assigned to the REF CURSOR or the COLLECTION, so that you can collect them at your output parameter and use it for your further processing.


Thank you,
Boobal Ganesan


Thanks for looking into this. I could not assign a REF CURSOR for pl/sql collection as I had strong ref cursor. Do let me know how I can return collection from the loop each time.
Go to the top of the page
 
+Quote Post
boobal_ganesan
post Oct 12 2017, 01:19 AM
Post #5


Advanced Member
***

Group: Members
Posts: 85
Joined: 6-June 16
From: India
Member No.: 51,370



Hello Roger,

There shouldn't be a problem when you are assigning a strong REF CURSOR into a collection.

Do you mean a WEAK REF CURSOR?

Post us your complete code for analyzing.


Thank you,
Boobal Ganesan


--------------------
Check out my new Advanced PL/SQL book here - http://www.rampant-books.com/book_1701_pls...initive_ref.htm
Go to the top of the page
 
+Quote Post
Roger_67
post Oct 13 2017, 03:23 PM
Post #6


Newbie
*

Group: Members
Posts: 3
Joined: 10-October 17
Member No.: 52,200



QUOTE (boobal_ganesan @ Oct 12 2017, 01:19 AM) *
Hello Roger,

There shouldn't be a problem when you are assigning a strong REF CURSOR into a collection.

Do you mean a WEAK REF CURSOR?

Post us your complete code for analyzing.


Thank you,
Boobal Ganesan


I need to return data using type object or a collection. And I want to definitely send them in bulk. Receiver is taking it through JDBC . So whatever I use here should be supported by JDBC also. I need to do bulk fetch into a collection object and also not fill collection in a loop, should be filled in bulk.

Thanks for spending time on this !!
Go to the top of the page
 
+Quote Post
boobal_ganesan
post Oct 13 2017, 11:23 PM
Post #7


Advanced Member
***

Group: Members
Posts: 85
Joined: 6-June 16
From: India
Member No.: 51,370



QUOTE (Roger_67 @ Oct 14 2017, 01:53 AM) *
I need to return data using type object or a collection. And I want to definitely send them in bulk. Receiver is taking it through JDBC . So whatever I use here should be supported by JDBC also. I need to do bulk fetch into a collection object and also not fill collection in a loop, should be filled in bulk.

Thanks for spending time on this !!


Hello Roger,

I understood your requirement, but why are you desperately trying to use the collections when you can use REF CURSORs?

Let me tell you about the pros and cons of the both,

Collections:

Pros:

1) Effective when you want a small PL/SQL table for a temporary purpose like a temporary look up table.
2) Any row can be operated at any time.

Cons:
1) If the data is more, your PGA fills up fast, so not recommended for large data processing.
2) When you want to return it as OUT parameter to either JAVA or any other PL/SQL, if your row set is more, its a pain in the PGA.


REF CURSOR:

Pros:

1) Unlike collection, the REF CURSOR doesnt send the complete rows but it just sends the pointer to the private area where are rows are stored initially when you create the cursor.

2) The main reason for the creation of the REF CURSOR is to send record sets between PL/SQL and PL/SQL or PL/SQL and Java, effectively.

Cons:

1) You cannot process any row you like. You just have to flow through all the rows in their order.


So, learning all these, please understand that a BULK COLLECT collection is not faster than a REF CURSOR when you are trying to send record sets to Java from PL/SQL. Oracle has created the REF CURSOR just for that purpose! Its super fast and efficient.

Note: OPEN your ref cursor in your PL/SQL but dont close it inside the PL/SQL itself! From your Java, read the REF CURSOR by looping it, and once you are done reading all the data, CLOSE the cursor. If you dont close your cursor and if the number of open cursors per session increases the limit set in the OPEN_CURSORS parameter, your code may fail.


Thank you,
Boobal Ganesan


--------------------
Check out my new Advanced PL/SQL book here - http://www.rampant-books.com/book_1701_pls...initive_ref.htm
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 23rd October 2017 - 03:42 PM