?
Solved

Outputting a rowtype

Posted on 2000-03-24
8
Medium Priority
?
1,611 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
[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
  • 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

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

Accepted Solution

by:
sbenyo earned 804 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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

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…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
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.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

770 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