Print Record fields in PL/SQL

Question

How can I print all the fields of a record variable in PL/SQL. The record variable has got lots of fields so is there a better way than printing each field? Also tried dynamic sql but didn't help.

Answer 1

Building on Ollies use of dbms_output, but for to dynamically go through the cursor

set up for test

/*create table temp (aa varchar2(50) , bb number , cc date ) ;

insert into temp (aa,bb,cc)
  select chr(level+100) , level, sysdate+level
    from dual
     connect by level < 15 ;
/
*/

---

Block to show the test (this assumes 11g)

set serveroutput on
declare
   l_cur SYS_REFCURSOR ;

    PROCEDURE CursorOutput(
                            p_refcursor        IN OUT SYS_REFCURSOR
                         )  
    AS
        l_desc          DBMS_SQL.DESC_TAB ;
        l_cols          BINARY_INTEGER ;
        l_cursor        BINARY_INTEGER ;
        v_varchar2      VARCHAR2( 4000 ) ;
        v_number        NUMBER ;
        v_date          DATE ;
        l_data          varchar2( 32767 ) ;
        l_columnValue   VARCHAR2( 32767 ) ;
        l_processedRows Number := 0;
    BEGIN

        /* Convert refcursor "parameter" to DBMS_SQL cursor... */
        l_cursor := DBMS_SQL.TO_CURSOR_NUMBER( p_refcursor );
        /* Describe the cursor... */
        DBMS_SQL.DESCRIBE_COLUMNS( l_cursor, l_cols, l_desc );

        /* Define columns to be fetched. We're only using V2, NUM, DATE for example...
        for a complete list of the col_types this link is accessible.
        http://download.oracle.com/docs/cd/B10501_01/server.920/a96540/sql_elements2a.htm#45504
        http://forums.oracle.com/forums/thread.jspa?threadID=912475
        if not a usable type, will throw new exception
        */
         FOR i IN 1 .. l_cols LOOP
             IF l_desc(i).col_type = 2 THEN
               DBMS_SQL.DEFINE_COLUMN(l_cursor, i, v_number);
            ELSIF l_desc(i).col_type = 12 THEN
               DBMS_SQL.DEFINE_COLUMN(l_cursor, i, v_date);
            ELSif l_desc(i).col_type = 01 or l_desc(i).col_type = 96 then
               DBMS_SQL.DEFINE_COLUMN(l_cursor, i, v_varchar2, 4000);
            else
                --raise an exception if the user's query contains a datatype not (yet) supported by this procedure
                RAISE_APPLICATION_ERROR(-20000, 'Invalid Data Type for conversion to delimited file. {' || l_desc(i).col_name || '}');
            END IF;
          END LOOP;


        /* -- print out the column names if desired
             FOR i IN 1 .. l_cols LOOP
                     dbms_output.put_line('** ' || l_desc(i).col_name) ;
             END LOOP;
        */

         /* Fetch all data... */
         WHILE DBMS_SQL.FETCH_ROWS(l_cursor) > 0 LOOP
             dbms_output.put_line('LINE: '  || l_processedRows || '');
             FOR i IN 1 .. l_cols LOOP
                 if l_desc(i).col_type = 12 THEN --we are in a date
                    DBMS_SQL.COLUMN_VALUE(l_cursor, i, v_date);
                    v_varchar2 := to_char(v_date , 'dd-MON-yyyy' ) ;
                 elsif  l_desc(i).col_type = 2 THEN --we are in a number
                    DBMS_SQL.COLUMN_VALUE(l_cursor, i, v_number);
                    v_varchar2 := to_char(v_number) ;
                 else --treat it as a string (should be varchar2,char,etc)
                    DBMS_SQL.COLUMN_VALUE(l_cursor, i, v_varchar2);
                    IF v_varchar2 IS NOT NULL THEN
                       v_varchar2 := '"' || v_varchar2 || '"' ;
                       ELSE
                       v_varchar2 := '';
                    END IF ;
                 end if ;
                 dbms_output.put_line(l_desc(i).col_name || '=>' || v_varchar2) ;
             END LOOP;
             l_processedRows := l_processedRows + 1 ;
          END LOOP;

          dbms_sql.close_cursor(l_cursor);
          dbms_output.put_line('I found and processed  '  || l_processedRows || ' rows .');

    END;

begin
        open l_cur for select * from temp;

        CursorOutput(p_refcursor => l_cur) ;

end ;
/

---

will give you this result

LINE: 0
AA=>"e"
BB=>1
CC=>04-JAN-2012
LINE: 1
AA=>"f"
BB=>2
CC=>05-JAN-2012
LINE: 2
AA=>"g"
BB=>3
CC=>06-JAN-2012
LINE: 3
AA=>"h"
BB=>4
CC=>07-JAN-2012
LINE: 4
AA=>"i"
BB=>5
CC=>08-JAN-2012
LINE: 5
AA=>"j"
BB=>6
CC=>09-JAN-2012
LINE: 6
AA=>"k"
BB=>7
CC=>10-JAN-2012
LINE: 7
AA=>"l"
BB=>8
CC=>11-JAN-2012
LINE: 8
AA=>"m"
BB=>9
CC=>12-JAN-2012
LINE: 9
AA=>"n"
BB=>10
CC=>13-JAN-2012
LINE: 10
AA=>"o"
BB=>11
CC=>14-JAN-2012
LINE: 11
AA=>"p"
BB=>12
CC=>15-JAN-2012
LINE: 12
AA=>"q"
BB=>13
CC=>16-JAN-2012
LINE: 13
AA=>"r"
BB=>14
CC=>17-JAN-2012
I found and processed  14 rows .

I had done something similar to this to dynamically build a csv file utilizing these two links as sources http://www.oracle-developer.net/display.php?id=505 http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:88212348059

Depending on what you are going for, however, you may just want to run it in SQL Developer (or Toad) and export the results!

Answer 2

If it's a PL/SQL block that you are running in an IDE then you could at a pinch use DBMS_OUTPUT to output the values.

http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_output.htm

For example:

SET SERVEROUTPUT ON

DECLARE
   -- Define the record
   TYPE test_rectype IS RECORD (
      field1 NUMBER,
      field2 VARCHAR2
   );
   -- Define a variable for the record
   test_rec TEST_RECTYPE;
BEGIN
   -- Populate the record
   test_rec.field1 := 1;
   test_rec.field2 := 'my value';
   -- Enable the DBMS_OUTPUT
   DBMS_OUTPUT.enable(1000000);
   -- Send the output to the buffer
   DBMS_OUTPUT.put_line('Field1: '||test_rec.field1||', Field2: '||test_rec.field2);
END;

There is more to DBMS_OUTPUT so take a look at the docs from the link above.

Alternatively, you could write the values to a file using UTL_FILE.

http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/u_file.htm

Hope it helps...