Re: Iterating through array as input parameter to stored procedure.

On 5 Jun 2006 09:26:20 -0700, asgars_at_gmail.com wrote:

>Hi,
>I am passing an array into a stored procedure. Now i want to iterate
>through this array. This can very well be done by For Loop.
>But my problem is:
>I have to run the SELECT query based on each element in array (i.e. as
>many times as the number of elements in the array) and i want to
>accumulate all the rows retreived from each query into a cursor so that
>i can get it into a Dataset finally.
>I am stuck as donno how to proceed in this....
>Please can anyone help..?
>
>Or is there a better way to do it??

CAST the array to a TABLE
 do not exactly remember the syntax, http://asktom.oracle.com has examples

store the result in a collection and CAST to a TABLE in a SELECT with a REF CURSOR.

(Maybe if you manage to get the input array as a table you don't need that anymore)

Your current approach will likely be unscalable.

--
Sybrand Bakker, Senior Oracle DBA