Re: A PL/SQL Loop Question

mtek_at_mtekusa.com wrote:
> Hi Everyone,
>
> I have this weird thing I have to do in PL/SQL and cannot figure it
> out.
>
> We are running 8i at the moment. I need to use dynamic SQL for this.
> Our application receives some parameters from a website. On the
> website the user will choose which columns of data they want to
> display. The information is received by the procedure via an array.
>
> I need to go through each element of the array, using it's contents,
> and gather some data from a table, and then return the information
> back to PHP.
>
> So, I have an unknown number of items that I need to select, as well
> as an unknown number of items which will be returned by the query.
>
> My thought was to set up a 2 dimentional array and first loop through
> the number of items in the array. For each item I'd construct a
> select statement and retrieve the column from the table. So, I'd come
> up with an array that would have rows and columns of everything
> needed.
>
> I was going to use 'EXECUTE IMMEDIATE' to execute the query. I am
> having trouble with 2 things: First is if Oracle 8i supports multi-
> dimentional arrays. The second thing is since I do not know how many
> rows I am retrieving from the table, how can I construct the proper
> loop?
>
> A weird thing and hard to explain. I can try to be more specific if
> needed.
>
> Thank you.
>
> John

Take your array and first determine its size. That's easy. Then with a FOR loop run through one time and construct a single SQL statement that returns everything you need.

Look at this for an idea of how to do that: http://www.psoug.org/reference/nds.html
scroll down to: "Demonstration Of Dynamic SQL Statement Creation"

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org