Output Column Names and Values without knowing them...

Posted on 2011-02-23
Last Modified: 2013-12-07
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.
Question by:gswitz
LVL 73

Assisted Solution

sdstuber earned 400 total points
ID: 34965755
you could query all_tab_cols to get the list of columns for each table
LVL 23

Assisted Solution

wdosanjos earned 50 total points
ID: 34965785
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).

Author Comment

ID: 34966606
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.
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

LVL 28

Assisted Solution

by:Naveen Kumar
Naveen Kumar earned 50 total points
ID: 34967300
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.
LVL 73

Accepted Solution

sdstuber earned 400 total points
ID: 34969095
using dbms_sql you can reference column values by number
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34969619
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:

Author Closing Comment

ID: 34970419
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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to take different types of Oracle backups using RMAN.

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question