Dynamic SQL - Cursor How To

[wellster34]

Hi,

I need to be able to create a cursor for a select statement so that I can process each record one by one.

Here are the catches:
* The Select statement is not hardcoded since it is dynamic. So, I do not know the tables or columns to be used.

Is there a way?

Thanks

 

[sem]

You may use REF CURSOR but knowing the structure of result set is a must to be able to fetch cursor to a variable for further processing.



Regards, Dima

http://seminihin.narod.ru

 

[lewisp]

Ref cursors are infinately preferrable to pure dynamic SQL, but if you do not know the structure you will need to use 'method 4' with the DBMS_SQL package. This will include a DBMS_SQL.PREPARE statement to identify and query an unknown number of columns. You will need to check your Oracle documentation for a more thorough explanation of this method.