Solved

Outputting a rowtype

Posted on 2000-03-24
8
1,578 Views
Last Modified: 2012-05-04
Is there a way to output a rowtype variable generally?

For instance, I want to have a cursor which selects into my rowtype variable, and I want to output every column. But the table structure may change, so I would like to have it still output all columns, without having to change anything.

The cursor has select * - so thats covered.
The variable is of type ROWTYPE, so that covers any changes.

But printing out the results doesn't seem to be straight forward...
I can't seem to use any sort of loop through the columns by number?
Can I use the user_tab_columns table and somehow integrate the column names with the rowtype variable to output all columns?

What I need is something like
select * from table into rowtype_variable;

for i = 1 to rowtype_variable.num_columns

  output rowtype_variable.column(i);
end for;

(Can it be that simple?)
0
Comment
Question by:rderidder
  • 4
  • 3
8 Comments
 
LVL 4

Expert Comment

by:syakobson
ID: 2655337
What you are looking for is dynamic sql and is not "that simple" but is not rocket science either. Especially in 8.i where you have Native Dynamic SQL.
Prior to 8.i you would have to use DBMS_SQL package. Then, assuming you are on 8.0, you can issue DBMS_SQL.DESCRIBE and get count and full info on columns in dynamic query. Then output retrieved values in a loop based on the datatype.

Solomon Yakobson.
0
 

Author Comment

by:rderidder
ID: 2660943
I don't quite understand the "output retrieved values in a loop based on the datatype".
COuld you include a quick example?

0
 

Author Comment

by:rderidder
ID: 2661094
Adjusted points from 200 to 201
0
 
LVL 5

Expert Comment

by:sbenyo
ID: 2661530
I took it as a challenge and wrote a procedure is PL/SQL to output a given table's rows.
You can use it as part of your script, take bits or change it to suit your needs.
The actual printing is done using dbms_output, and can be changed to any format you want.

I've used dynamic SQL to extract the different columns and types.
I used the regular DBMS_SQL and not Native SQL as in oracle 8i so it should also work in previous versions.

Try creating a simple table, run the procedure to create the script below.
Use the procedure to test:

SQL>set serveroutput on;  --> for enabling output in the client

SQL>execute printrow('XXX');

XXX - stands for table name.

You may change it to suite your needs.

One drawback in this program, it may not support all datatypes like RAW, BLOB etc...

Please tell me if it helps.

Here is the code:

create or replace procedure PrintRow(table_name in varchar2) is

  type EmptyCursorType is REF CURSOR;
  cur      EmptyCursorType;

  cursor c1(name varchar2) is
     select column_name,data_type from  
        user_tab_columns
     where table_name = name;

  Statement      varchar2(200);

  cid      integer;
  col_value      varchar2(30);
  rows             integer;
  table_rows      integer;
  table_rowid      rowid;
  rows_cid      integer;

begin

      -- Declare and open rows cursor

 rows_cid := DBMS_SQL.OPEN_CURSOR;
 Statement := 'select rowid from  
   '||table_name;
 DBMS_SQL.PARSE    
   (rows_cid,Statement,DBMS_SQL.V7);
 rows := DBMS_SQL.EXECUTE(rows_cid);
 DBMS_SQL.DEFINE_COLUMN_ROWID
   (rows_cid,1,table_rowid);

 while DBMS_SQL.FETCH_ROWS(rows_cid) > 
   0 loop
            
   DBMS_SQL.COLUMN_VALUE_ROWID
    (rows_cid,1,table_rowid);


   for c1_rec in c1(table_name) loop
            
            -- Select the column
     cid := DBMS_SQL.OPEN_CURSOR;
     if c1_rec.data_type = 'NUMBER' then
       Statement := 'select to_char
         ('||c1_rec.column_name||')
             from '||table_name||
           ' where rowid =
           '''||table_rowid||'''';
     else
      Statement := 'select
          '||c1_rec.column_name||' from
          '||table_name||
          ' where rowid =
          '''||table_rowid||'''';
     end if;

     DBMS_SQL.PARSE
       (cid,Statement,DBMS_SQL.V7);
     DBMS_SQL.DEFINE_COLUMN
       (cid,1,col_value,30);

     rows := DBMS_SQL.EXECUTE(cid);
     if (DBMS_SQL.FETCH_ROWS(cid) > 0)
     then

      DBMS_SQL.COLUMN_VALUE
          (cid,1,col_value);
      DBMS_OUTPUT.PUT(col_value||' ');
     end if;
     DBMS_SQL.CLOSE_CURSOR(cid);

   end loop;
      
   DBMS_OUTPUT.NEW_LINE;
 end loop;
 
 DBMS_SQL.CLOSE_CURSOR(rows_cid);

end PrintRow;

0
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 5

Accepted Solution

by:
sbenyo earned 201 total points
ID: 2661538
The Statement:= lines are seperated here to more than one line.
Copy them as one line with no additional spaces to avoid problems.
0
 

Author Comment

by:rderidder
ID: 2661739
Perfect.
I was almost there myself.. But the DEFINE_COLUMN line was getting me..

Thanks a bunch.
0
 
LVL 5

Expert Comment

by:sbenyo
ID: 2661764
I took it as a challenge and wrote a procedure is PL/SQL to output a given table's rows.
You can use it as part of your script, take bits or change it to suit your needs.
The actual printing is done using dbms_output, and can be changed to any format you want.

I've used dynamic SQL to extract the different columns and types.
I used the regular DBMS_SQL and not Native SQL as in oracle 8i so it should also work in previous versions.

Try creating a simple table, run the procedure to create the script below.
Use the procedure to test:

SQL>set serveroutput on;  --> for enabling output in the client

SQL>execute printrow('XXX');

XXX - stands for table name.

You may change it to suite your needs.

One drawback in this program, it may not support all datatypes like RAW, BLOB etc...

Please tell me if it helps.

Here is the code:

create or replace procedure PrintRow(table_name in varchar2) is

  type EmptyCursorType is REF CURSOR;
  cur      EmptyCursorType;

  cursor c1(name varchar2) is
     select column_name,data_type from  
        user_tab_columns
     where table_name = name;

  Statement      varchar2(200);

  cid      integer;
  col_value      varchar2(30);
  rows             integer;
  table_rows      integer;
  table_rowid      rowid;
  rows_cid      integer;

begin

      -- Declare and open rows cursor

 rows_cid := DBMS_SQL.OPEN_CURSOR;
 Statement := 'select rowid from  
   '||table_name;
 DBMS_SQL.PARSE    
   (rows_cid,Statement,DBMS_SQL.V7);
 rows := DBMS_SQL.EXECUTE(rows_cid);
 DBMS_SQL.DEFINE_COLUMN_ROWID
   (rows_cid,1,table_rowid);

 while DBMS_SQL.FETCH_ROWS(rows_cid) > 
   0 loop
            
   DBMS_SQL.COLUMN_VALUE_ROWID
    (rows_cid,1,table_rowid);


   for c1_rec in c1(table_name) loop
            
            -- Select the column
     cid := DBMS_SQL.OPEN_CURSOR;
     if c1_rec.data_type = 'NUMBER' then
       Statement := 'select to_char
         ('||c1_rec.column_name||')
             from '||table_name||
           ' where rowid =
           '''||table_rowid||'''';
     else
      Statement := 'select
          '||c1_rec.column_name||' from
          '||table_name||
          ' where rowid =
          '''||table_rowid||'''';
     end if;

     DBMS_SQL.PARSE
       (cid,Statement,DBMS_SQL.V7);
     DBMS_SQL.DEFINE_COLUMN
       (cid,1,col_value,30);

     rows := DBMS_SQL.EXECUTE(cid);
     if (DBMS_SQL.FETCH_ROWS(cid) > 0)
     then

      DBMS_SQL.COLUMN_VALUE
          (cid,1,col_value);
      DBMS_OUTPUT.PUT(col_value||' ');
     end if;
     DBMS_SQL.CLOSE_CURSOR(cid);

   end loop;
      
   DBMS_OUTPUT.NEW_LINE;
 end loop;
 
 DBMS_SQL.CLOSE_CURSOR(rows_cid);

end PrintRow;

0
 
LVL 5

Expert Comment

by:sbenyo
ID: 2661771
Also recommeded to add this line just after the begin, to raise the output buffer limit of dbms_output:

begin

   DBMS_OUTPUT.ENABLE(x);
     .
     .
     .

x - the buffer size in bytes.
The default value is 2000, and is not enough for big tables.



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
Error executing command from server 6 41
Processing of multiple cursor 6 35
Oracle query output question 4 36
'G_F01' is not a procedure or is undefined 3 12
Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
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 videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

930 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

11 Experts available now in Live!

Get 1:1 Help Now