Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Addressing columns in a record with a variable name?

BerniePAug 3 2005 — edited Aug 3 2005
I would like to dynamicly loop through a series of columns in a record whose structure I won't know at complile time (and even if I did there are so many columns I don't want to hand code them all). But whose column names I have from a metadata table. For instance
declare
my_data_record data_table%rowtype;
v_column_name metadatatable.column_name%type;
v_datatype metadatatable.datatype%type;
cursor data_cur is select * from data_table;
cursor column_cur is select column_name, datatype
from metadatatable where table_name = upper('data_table') ;
begin
open data_cur ;
loop
fetch data_cur into my_data_record;
exit when data_cur%notfound;
open column_cur;
loop
fetch column_cur into v_column_name, v_datatype ;
exit when column_cur%notfound;
insert into normalized_table values
(my_data_record.key_column, v_column_name,
to_char(data_cur.v_column_name)
)
end loop ;
close index_col_cur;
end loop;
end;

Now I know this isn't optimized, for instance I could put the list of columns in an array and then not have to do a db access for each data record. But what I want to know is, is there a way to address a column in a record with a variable???
Something like
data_cur.v_column_name
or
data_cur[v_column_name]
Thanks in advance.

Bernie
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Aug 31 2005
Added on Aug 3 2005
5 comments
612 views