Output Column Names and Values without knowing them...
Posted on 2011-02-23
I want to be able to output column name/value pairs without having to explicitly define them. I have to execute this for 72 tables in different environments where the tables do not all have the same columns.
FOR trans_table IN
(SELECT * FROM all_tables WHERE SUBSTR(table_name,-3,3)='TXN')
sql_string:='select rownum r, t1.* from mySchema.' ||
trans_table.table_name || ' t1 where trans_id=' || trans_id;
dbms_output.put_line('TABLE: ' || trans_table.table_name);
FOR transact IN sql_string
dbms_output.put_line('ROW: ' || transact.R);
FOR col IN transact.columns
dbms_output.put_line(col.COLUMN_NAME || col.COLUMN_VALUE);
Obviously, this doesn't work, but if there is a way to do it short of being explicit, it would really help.