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

dananrg_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

Cursor loops such as in your example have been obsolete since the introduction of Oracle 9i.

BULK COLLECT into an array indexed by binary integer and you can do this with ease (Morgan's Library at www.psoug.org).

-- 
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org