Addressing columns in a record with a variable name?
BerniePAug 3 2005 — edited Aug 3 2005I 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