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

[dan...@yahoo.com]

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

[Thomas Olszewicki]

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

[DA Morgan]

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
damo...@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

[dan...@yahoo.com]

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

Thanks Daniel and Thomas. Is there a specific example at:
http://www.psoug.org/reference/array_processing.html
which will do what I want?

Tweaked and tried one example and it failed. Many other of the
examples on the page do a SELECT single_column rather than a SELECT *.

Also, I'm still at 9i R2. So I would need an example that works at
this release. Most of your examples are classified as 10g or 11i.

Dana

[Frank van Bortel]

Why would you want that?

Is this another, doomed to fail, attempt to write
"universal" code?!?

--

Regards,
Frank van Bortel

Top-posting in UseNet newsgroups is one way to shut me up

[DA Morgan]

The first "FAST_WAY" example does it ... look at the FOR look between
the BULK COLLECT and the FORALL.

[Malcolm Dew-Jones]

DA Morgan (damo...@psoug.org) wrote:

I am not sure how BULK COLLECT will help him to print out the individual
columns without knowing the name of each column.

[DA Morgan]

Malcolm Dew-Jones wrote:

> : 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).
>
> I am not sure how BULK COLLECT will help him to print out the individual
> columns without knowing the name of each column.

You don't need to know the column names ... just look through the
indices of the array.

[William Robertson]

No, you cannot iterate through attributes of a record or object.

[dan...@yahoo.com]

Daniel, I'm going to look for your code and try it out today. If in
the mean time you want to post a snippet here, that would be great.
Evidently I'm not the only one confused about accomplishing this task
through PL/SQL.

If it can be done, it would be great to post the code here for others
to learn from.

[DA Morgan]

Look at the following demo:
http://www.psoug.org/reference/dbms_sql.html

Note the defined data types.
Read through the various procedure names.

It isn't easy ... but it can be done unless I am totally
misunderstanding what you are trying to accomplish.

[Thomas Olszewicki]

Dana,
As I said before, the only method I know of is to use :
DBMS_SQL with describe_columns parocedure.
Look at the examples:
1. http://www.oracle.com/technology/oramag/code/tips2003/042003.html
2. http://www.oracle.com/technology/oramag/oracle/06-may/o36plsql.html
search for :Obtaining the Names of Columns in a Dynamic Select

HTH
Thomas