Solved

Outputting a rowtype

Posted on 2000-03-24
8
1,569 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
Comment Utility
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
Comment Utility
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
Comment Utility
Adjusted points from 200 to 201
0
 
LVL 5

Expert Comment

by:sbenyo
Comment Utility
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
Comment Utility
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
Comment Utility
Perfect.
I was almost there myself.. But the DEFINE_COLUMN line was getting me..

Thanks a bunch.
0
 
LVL 5

Expert Comment

by:sbenyo
Comment Utility
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
Comment Utility
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

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.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Oracle SQL Select Statement 19 58
Need help with Oracle syntax 4 39
Pl/SQL Query 31 61
Oracle RMAN Database Restore 5 28
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
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…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

771 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