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

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??
>

I would cast the VARRAY into a TABLE and join the beast with that subquery. Let the DBMS system worry whether nested loop join is really the right approach (which is what you force by keeping it in PL/SQL).

just my 2 cents as a theoretician
Serge

-- 
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ondemandbusiness/conf2006/