Solved

Outputting a rowtype

Posted on 2000-03-24
8
1,608 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

Enroll in June's Course of the Month

June's Course of the Month is now available! Every 10 seconds, a consumer gets hit with ransomware. Refresh your knowledge of ransomware best practices by enrolling in this month's complimentary course for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

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…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
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.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

696 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