Re: A PL/SQL Loop Question

On Wed, 07 Nov 2007 12:19:57 -0800, 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.

Is there a particular reason you need to use dynamic SQL? Usually, one SQL statement can be constructed to handle most cases.

>Our application receives some parameters from a website. On the
>website the user will choose which columns of data they want to
>display.

You can still query all the COLUMNs and just display that which you require, unless this is a great deal of data. Or, if there is a maximum amount of COLUMNS, CASE can be used to grab the required ones.

> The information is received by the procedure via an array.

Is there a particular reason an array is used? If it is guaranteed to be less than one thousand entries, a string may work an an IN() clause.

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

An example query would be helpful. How it is being used would most likely make a big difference.

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

Returning the data into a CURSOR might be the best bet.

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

I think an example would explain a great deal more. Would you be able to post some example code?

B.

>
>Thank you.
>
>John
Received on Thu Nov 08 2007 - 08:01:51 CST