Solved

Output Column Names and Values without knowing them...

Posted on 2011-02-23
7
1,177 Views
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.

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.
0
Comment
Question by:gswitz
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 74

Assisted Solution

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

Assisted Solution

by:wdosanjos
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).
0
 

Author Comment

by:gswitz
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')
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
Turn Insights Into Action

You’ve already invested in ITSM tools, chat applications, automation utilities, and more. Fortify these solutions with intelligent communications so you can drive business processes forward.

With xMatters, you'll never miss a beat.

 
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.
0
 
LVL 74

Accepted Solution

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

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:
http://www.experts-exchange.com/Database/Oracle/Q_26843082.html?#a34966249
0
 

Author Closing Comment

by:gswitz
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.
0

Featured Post

Turn Insights Into Action

You’ve already invested in ITSM tools, chat applications, automation utilities, and more. Fortify these solutions with intelligent communications so you can drive business processes forward.

With xMatters, you'll never miss a beat.

Question has a verified solution.

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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

717 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