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

<jmayer_at_ratundtat.com> wrote in message news:1127207242.558470.103050_at_g44g2000cwa.googlegroups.com...
> 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
>

that would be nice... have you been working with MS-Access collections?

can't do that directly, but there's a couple of options, depending on your performance requirements and how dynamic your queries are going to be

you'd have to do something with dynamic SQL (dbms_sql can give you a describe of the columns) or perhaps one of the XML packages

you may even want to consider dynamically generating an anonymous PL/SQL block and submitting that to EXECUTE IMMEDIATE

++ mcs Received on Tue Sep 20 2005 - 07:05:19 CDT