• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1190
  • Last Modified:

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.

  trans_id   NUMBER=1;
  sql_string VARCHAR2(4000);
  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);
      END LOOP;

Obviously, this doesn't work, but if there is a way to do it short of being explicit, it would really help.
4 Solutions
you could query all_tab_cols to get the list of columns for each table
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).
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')
 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.
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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.
using dbms_sql you can reference column values by number
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:
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.

Featured Post


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now