Re: Looping through column values in a record without specifying column names?

On Mar 4, 10:38 am, dana..._at_yahoo.com wrote:
> In PL/SQL, is there a way to loop through column values in a record
> without specifying column names in advance? e.g. like using an index
> number in an array?
>
> The code below will output a value for the column specified; but I've
> not found a way to create an inner loop and iterate through the record
> itself.
>
> DECLARE
>
> cursor cur is
> select * from my_table;
>
> BEGIN
>
> for rec in cur
> loop
>      dbms_output.put_line(rec.my_column);
> end loop;
>
> END;
>
> Tried inserting an inner loop something like the following, and it
> failed:
>
>              for x in rec
>              loop
>                  dbms_output.put_line(x)
>              end loop;
>
> ... and it failed:
>
> ERROR at line 7:
> ORA-06550: line 7, column 12:
> PLS-00456: item 'REC' is not a cursor
> ORA-06550: line 7, column 3:
> PL/SQL: Statement ignored
>
> So I can't use a RECORD in a FOR IN loop. How can I accomplish what I
> want to do some other way?
>
> Thanks.
>
> Dana

I know only one such method to use.
Use dynamic sql with DBMS_SQL package.
You can retrieve column names using procedure describe_columns. HTH
Thomas Received on Tue Mar 04 2008 - 14:56:47 CST