<mikeg13_at_gmail.com> wrote in message
news:1153836387.177135.244110_at_i3g2000cwc.googlegroups.com...
> I am creating dynamic SQL, from the ALL_TAB_COLS table, and the output
> is unknown until runtime. My SELECT statement may select 4 columns, 20
> columns, or 200. Once I have built this SQL, how can I run it into a
> cursor? How do I define that cursor?
>
> It would be easy enough to do if I could get this to work, but
> evidently it does not, saying that it is finding LOOP when it is not
> expected.
>
> FOR x IN (v_sql_stmt)
> LOOP
> IF x.variable_name THEN dbms_output.put_line(x.variable_name); END
> IF;
> END LOOP;
>
> Using the OPEN-FETCH-CLOSE for cursors, how can I define the cursor
> without knowing the structure of the output record? It seems the
> %ROWTYPE is what I need, but that only works if you have a table to
> reference. I would like to create the rowtype based on the out put
> that my dynamic SQL would return. Any help or pointers would be
> appreciated.
>
> Mike
>
In general you should try to not do this type of thing if it is frequently
executed sql. However, the way to do it is with the dbms_sql package. That
package offers a nice set of functions and procedures to deal with dynamic
sql. (sort of an oci interface for pl/sql)
Jim
Received on Tue Jul 25 2006 - 09:39:21 CDT
