Solved

Outputting a rowtype

Posted on 2000-03-24
8
1,585 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
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

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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle 12c database link between pdb not working 20 76
Create table from select - oracle 6 41
Require data to appear on a single line 2 44
Help on model clause 5 31
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
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.
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

778 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