Output Column Names and Values without knowing them...

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.

DECLARE
  trans_id   NUMBER=1;
  sql_string VARCHAR2(4000);
BEGIN
  FOR trans_table IN
  (SELECT * FROM all_tables WHERE SUBSTR(table_name,-3,3)='TXN')
  LOOP
    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
    LOOP
      dbms_output.put_line('ROW: ' || transact.R);
      FOR col IN transact.columns
      LOOP
        dbms_output.put_line(col.COLUMN_NAME || col.COLUMN_VALUE);
      END LOOP;
    END LOOP;
  END LOOP;
END;

Obviously, this doesn't work, but if there is a way to do it short of being explicit, it would really help.
gswitzAsked:
Who is Participating?
 
sdstuberCommented:
using dbms_sql you can reference column values by number
0
 
sdstuberCommented:
you could query all_tab_cols to get the list of columns for each table
0
 
wdosanjosCommented:
With the information from all_tab_cols you can have your code generate the code block in a string and execute it dynamically (EXECUTE IMMEDIATE).
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
gswitzAuthor Commented:
but can I dynamically reference a cursor column either by integer or string...

for col in (select * from all_tab_cols where table_name='myTable')
loop
 dbms_output.put_line(col.column_name || ' ' || transact.(col.column_name??????);
 dbms_output.put_line(col.column_name || ' ' || transact.(col.col_number??????);
end loop;

I know about the table already. I could use it to generate a mile of PL/SQL that would kinda do what I want, but I'll have to create a version for each environment. I wanted something more elegant.
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
Not sure whether i have understood your requirement clearly or not. you can try something like this but it has limitations..

1) first fetch all the columns for a given table into columns_array ( plsql array ) in the order of column names
    example: assume your table has 3 columns ( name, dept, sal )
    so the array will have something like
    columns_array(1) = dept  
    columns_array(2) = name
    columns_array(3) = sal

2) then by using this same order frame the sql statement something like
    sql := 'select rownum r  '
    for x in 1..columns_array.count loop
    sql := sql || ',' || columns_array(1);
    end loop;
    sql := sql || ' from myemptable where .... ';
    so the sql itself now get data in the column order we wanted ( column names are sorted in ascending order.
    finally, the sql has to be something like 'select rownum r, dept, name, sql from myemptable where....'

3)  have a cursor for the sql in 2)
     have a record type as declared below...
     type myrec_type is
     ( col_1 varchar2(2000),
       ..
       col_n varchar2(2000));
      fetch the record from the cursor into the type.
      print something like this :  
    print_str := 'being ';
    for x in 1..columns_array.count loop
      print_str := ' dbms_output.put_line( ''' || column_array(x)  ;
      print_str := print_str || ' : ''' || 'col_' || x  || ');'  ;
   end loop;
    print_str := print_str || ' end; '
      execute immediate print_str

the above is not a good idea but this just kind of a way out if you do not have any other options. but i think if you tell your requirement clearly here, we can suggest something which is good and viable for use.
0
 
slightwv (䄆 Netminder) Commented:
I recently created a simple function to output any tables columns and values in XML ROW format.

If you want it in a different format you can tweak the XML to produce any output you want.

Check out:
http://www.experts-exchange.com/Database/Oracle/Q_26843082.html?#a34966249
0
 
gswitzAuthor Commented:
sdstuber ultimately gave me what I was looking for, but not before I wrote an anonymous block to create the extremely wordy 2300 line sproc where I explicitly use field names. Thanks, sdstuber, I'll put this trick in my bag and use it next time.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.