Solved

Output Column Names and Values without knowing them...

Posted on 2011-02-23
7
1,161 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
7 Comments
 
LVL 73

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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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 73

Accepted Solution

by:
sdstuber earned 400 total points
ID: 34969095
using dbms_sql you can reference column values by number
0
 
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:
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Run SQL statement in Microsoft Access 9 32
Oracle SQL 6 48
Queries 15 34
SQL Server CASE .. WHEN .. IN statement - Syntax issue 4 51
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

920 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now