OraFAQ Forum: SQL & PL/SQL » Bulk collect inside a loop

Home » SQL & PL/SQL » SQL & PL/SQL » Bulk collect inside a loop (Oracle9i, Windows)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Bulk collect inside a loop [message #546694]

Thu, 08 March 2012 04:16

rsreejithmenon
Messages: 9
Registered: February 2012
Location: Bangalore

Junior Member

Hi,

I have a select..bulk collect into clause which is inside a for loop as the query gets a parameter from the loop, Then, how to extend the collection rows for each iteration.

My collection is of a sql object type.

thanks
Sreejith

Report message to a moderator

Re: Bulk collect inside a loop [message #546696 is a reply to message #546694]

Thu, 08 March 2012 04:33

Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

You do not extend the collection, Oracle does it for you.
Of course, it erases what was inside before.

Regards
Michel

Report message to a moderator

Re: Bulk collect inside a loop [message #546698 is a reply to message #546696]

Thu, 08 March 2012 04:39

rsreejithmenon
Messages: 9
Registered: February 2012
Location: Bangalore

Junior Member

then how to handle it if we want data from each iteration in collection? Cannot choose bulkbinding?

Thanks
Sreejith

Report message to a moderator

Re: Bulk collect inside a loop [message #546699 is a reply to message #546698]

Thu, 08 March 2012 04:58

Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

"bulk collect" gives you the data in a collection at each iteration.

Regards
Michel

Report message to a moderator

Re: Bulk collect inside a loop [message #546700 is a reply to message #546699]

Thu, 08 March 2012 05:06

rsreejithmenon
Messages: 9
Registered: February 2012
Location: Bangalore

Junior Member

Thanks Michel,
But, what I require is to append the datasets at the bottom of collection for each iteration and use collection later outside the loop.

thanks
Sreejith

Report message to a moderator

Re: Bulk collect inside a loop [message #546701 is a reply to message #546694]

Thu, 08 March 2012 05:10

flyboy
Messages: 1903
Registered: November 2006

Senior Member

Then you have to use two collections - the temporary one for obtaining data with BULK COLLECT, the permanent one for storing final result set. Using MULTISET UNION operator for continuous adding values from the first to the second one should be the best choice.
You may find more details in PL/SQL User's Guide and Reference book for your Oracle version. It is available with other Oracle documentation e.g. online on http://tahiti.oracle.com/
Please, consult it.

Report message to a moderator

Previous Topic:	Printing tables side by side
Next Topic:	how to write Analytic function without aggregate?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Sep 10 16:29:02 CDT 2025