Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: A PL/SQL Loop Question

Re: A PL/SQL Loop Question

From: DA Morgan <>
Date: Wed, 07 Nov 2007 14:13:27 -0800
Message-ID: <> wrote:

> On Nov 7, 3:19 pm, DA Morgan <> wrote:

>> 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:
>> scroll down to: "Demonstration Of Dynamic SQL Statement Creation"
>> --
>> Daniel A. Morgan
>> University of Washington
>> (replace x with u to respond)
>> Puget Sound Oracle Users
> Thank you Daniel.  What I really need is a 2 dimensional array.  The
> rows would be the rows from the database, and the columns would be the
> different values returned.  I can get the values individually, but I
> need to store each of them across the array.  From what I read though,
> Oracle 8i does not support anything like this......

You don't need a two dimensional array.

You are not doing anything that thousands, perhaps tens of thousands of us do. Just use two arrays.

Daniel A. Morgan
University of Washington (replace x with u to respond) Puget Sound Oracle Users Group Received on Wed Nov 07 2007 - 16:13:27 CST

Original text of this message