Dynamic SQL select list

I would like to know how to accomplish the following:

In dynamic SQL for version 8.0.x, you can parse a query, define its columns, execute the query,
and get the results using column_value. So far, no problems. What I would like to know is how
to dynamically create the columns. In other words, I don't know in advance how many columns will
be selected, so I can't use

  DBMS_SQL.DEFINE_COLUMN(v_cursor,1,v_FirstColumn)   DBMS_SQL.DEFINE_COLUMN(v_cursor,2,v_SecondColumn)....

Of course, the same goes for the DBMS_SQL.COLUMN_VALUE procedure.

I tried to get the columns supplied, parse them manually by comma and place them in a PL/SQL table,
then iterate through the collection as follows:

  FOR d IN 1..v_indx LOOP --v_indx represents the count of the columns obtained

    DBMS_SQL.DEFINE_COLUMN(v_cursor_single,d,v_temp);   END LOOP;   The problem with that was I kept getting the:

  ORA-06550: line 50, column 9:
  PLS-00307: too many declarations of 'DEFINE_COLUMN' match this call

error, for good reason.

Am I going down the wrong road, or does someone have a solution for this?

Many Thanks In Advance, Steve Received on Fri Sep 29 2000 - 16:24:09 CDT