 | |
Looping through column values in a record without specifying columnnames?
comp.databases.oracle.misc comp.databases.oracle.misc
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
Looping through column values in a record without specifying columnnames? -
03-04-2008
, 09:38 AM
#2
| |||
| |||
|
|
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 |
Use dynamic sql with DBMS_SQL package.
You can retrieve column names using procedure describe_columns.
HTH
Thomas
#3
| |||
| |||
|
|
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 |
Use dynamic sql with DBMS_SQL package.
You can retrieve column names using procedure describe_columns.
HTH
Thomas
#4
| |||
| |||
|
|
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 |
Use dynamic sql with DBMS_SQL package.
You can retrieve column names using procedure describe_columns.
HTH
Thomas
#5
| |||
| |||
|
|
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 |
Use dynamic sql with DBMS_SQL package.
You can retrieve column names using procedure describe_columns.
HTH
Thomas
#6
| |||
| |||
|
|
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 |
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@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
#7
| |||
| |||
|
|
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 |
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@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
#8
| |||
| |||
|
|
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 |
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@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
#9
| |||
| |||
|
|
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 |
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@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
#10
| |||
| |||
|
|
BULK COLLECT into an array indexed by binary integer and you can do this with ease (Morgan's Library atwww.psoug.org). |
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
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.