Re: How to accessing columns of a query-result by index

jmayer_at_ratundtat.com wrote:
> Hi there!
>
> In PL/SQL, I want to access the columns of my resultset by index. I'm
> dreaming of something like this:
>
> for rec_data in cursor (select * from postal_address) loop
>
> -- examining the structure of the resultset:
> for i in 1..rec_data%colcount loop
>
> dbms_output.putline(rec_data%column(i));
>
> end loop;
>
> end loop;
>
> To avoid misunderstandings: the expressions "rec_data%colcount" and
> rec_data%column(i) are only existing in my fantasy, i just want to know
> if it's possible to examine the unkown structure of resultset to handle
> the resultset in a dynamic way.
>
> Any ideas ?
>
> Using Oracle 9i, release 9.2.0.3.0
>
> Jens
>

For true dynamic sql, i.e. the structure returned completely unknown at design time, you'll have to use dbms_sql. However, that also means that you probably can't just pass a ref cursor around but you'll have to use the dbms_sql package throughout the dynamic part of your application (which will probably also pass sql-statements as varchar2 parameter). If you're not carefully with this aproach, you can kiss goodby your performance and scalability.

Regards,

Holger Received on Tue Sep 20 2005 - 06:54:20 CDT