Re: Dynamic SQL select list

I think you are getting stuck into 'type 4' dynamic sql there, and need to use the
'describe' call to find out how many columns the query has, and what types.

There is an example of doing this on
my website, and Thomas Kyte published
a (nicer) sample some time back, so
you could try searching his site too.

--

Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Steve wrote in message ...


>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

>

>

>