Oracle Scratchpad

November 26, 2020

print_table()

Filed under: Oracle,Problem Solving — Jonathan Lewis @ 9:40 am GMT Nov 26,2020

Many years ago Tom Kyte published a small PL/SQL procedure to output each row in a table as a list of (column_name , value). I often use the original version, even though Tom refined it several times over the years. From time to time the links in my blog that I’ve set up to point to Tom’s code have simply stopped working so I’ve decided to publish a reference copy on my blog in case newer readers need to find a copy and the current link above stops  working.

rem
rem     Script:         print_table.sql
rem     Dated:          Nov 2001
rem     Author:         Tom Kyte - from Expert One on One Oracle.
rem
rem     Purpose:
rem     Prints the results of a query one column per line.
rem
rem     Created as a 'authid current_user' so that it can
rem     access any table that the caller is allowed to access
rem     from the SQL*Plus prompt.
rem
rem     A suitably privilefed user could also make this publicly
rem     available through the public synonym and grant at the end
rem     of the script
rem
rem     Minor modifications by Jonathan Lewis to separate rows
rem     in the result set, and report the number of rows returned
rem

create or replace procedure print_table( p_query in varchar2 )
authid current_user
is
        l_theCursor     integer default dbms_sql.open_cursor;
        l_columnValue   varchar2(4000);
        l_status        integer;
        l_descTbl       dbms_sql.desc_tab;
        l_colCnt        number;
        l_rowcnt        number := 0;
begin
        execute immediate 'alter session set nls_date_format=''dd-mon-yyyy hh24:mi:ss'' ';

        dbms_sql.parse(  l_theCursor,  p_query, dbms_sql.native );
        dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );

        for i in 1 .. l_colCnt loop
                dbms_sql.define_column(l_theCursor, i, l_columnValue, 4000 );
        end loop;

        l_status := dbms_sql.execute(l_theCursor);

        while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
                for i in 1 .. l_colCnt loop
                        dbms_sql.column_value( l_theCursor, i, l_columnValue );
                        dbms_output.put_line(
                                rpad( l_descTbl(i).col_name, 30 )
                                || ' : ' || l_columnValue
                        );
                end loop;
                dbms_output.new_line;
                dbms_output.put_line( '-----------------' );
                dbms_output.new_line;
                l_rowcnt := l_rowcnt + 1;
        end loop;

        dbms_output.put_line(l_rowcnt || ' rows selected');

        execute immediate 'alter session set nls_date_format=''dd-mon-rr'' ';

exception
        when others then
                execute immediate 'alter session set nls_date_format=''dd-mon-rr'' ';
        raise;
end;
/

-- create public synonym print_table for print_table;
-- grant execute on print_table to public;

The reason I’m publishing this now is that I’m about to publish the third variant of a piece of code I wrote many years ago, and first blogged about in 2012.

12 Comments »

  1. + Another simple one-query variant: http://orasql.org/2013/04/02/sqlplus-tips-2/
    Scripts:
    https://github.com/xtender/xt_scripts/blob/master/print_table.sql
    https://github.com/xtender/xt_scripts/blob/master/print_last.sql

    based on xmltable():

    select *
    from 
       xmltable( 'let $i:=0
                  for $r at $i in /ROWSET/ROW
                    for $e at $j in $r/*
                       return element r {
                                  element i {data($i)}
                                , element j {data($j)}
                                , element key   { data($e/name()) }
                                , element val   { data($e/text()) }
                              }
                 '
                passing dbms_xmlgen.getxmltype(q'[ &query ]')
                columns 
                    i for ordinality
                ,   p_rownum  int path 'i'
                ,   p_field_n int path 'j'
                ,   p_key     varchar2(30) path 'key'
                ,   p_val     varchar2(30) path 'val'
       )
    

    Comment by Sayan Malakshinov — November 26, 2020 @ 10:09 am GMT Nov 26,2020 | Reply

    • Hi Jonathan & Sayan,

      Regarding the passing of the query as a parameter by using table(xmlsequence(cursor(&1))) ( like in script print_last.sql variant 2 )
      vs using dbms_xmlgen.getxmltype(q'[ &query ]’) , there are some interesting remarks in Stew Ashton’s post here:

      https://stewashton.wordpress.com/2018/05/18/print-a-table-with-one-column-name-value-per-row/

      and the comments in that post.

      For some reason, using CURSOR(&query) instead of a call to DBMS_XMLGEN does miss the columns having NULL values
      from the query XML result, not clear to me why.

      Best Regards,
      Iudith Mentzel

      Comment by iudith — November 27, 2020 @ 11:45 am GMT Nov 27,2020 | Reply

      • Iudith,

        Thanks for the comment and the link to Stew’s blog note.
        As you say, interesting comments

        Regards
        Jonathan Lewis

        Comment by Jonathan Lewis — November 27, 2020 @ 11:53 am GMT Nov 27,2020 | Reply

      • Hi Iudith,

        Yes, I remember that from the Steven’s post we both discussed in 2011: http://plsql-challenge.blogspot.com/2011/02/different-kind-of-challenge-for-your.html
        :)
        Usually I don’t need to NULLs in such output, because it consumes extra space, so we see less info on the screen. But it’s easy to enable NULL output: https://github.com/xtender/xt_scripts/blob/master/print_table_nulls.sql

        with
           function getxmltype(p_cursor sys_refcursor) return xmltype as
              h dbms_xmlgen.ctxHandle;
              x xmltype;
           begin
               h := dbms_xmlgen.newContext(p_cursor);
               dbms_xmlgen.setNullHandling(h, dbms_xmlgen.EMPTY_TAG);
               x := dbms_xmlgen.getXMLType(h);
               dbms_xmlgen.closecontext(h);
               return x;
           end;
        select *
        from 
           xmltable( 'let $i:=0
                      for $r at $i in /ROWSET/ROW
                        for $e at $j in $r/*
                           return element r {
                                      element i {data($i)}
                                    , element j {data($j)}
                                    , element key   { data($e/name()) }
                                    , element val   { data($e/text()) }
                                  }
                     '
                    passing getxmltype(cursor(&1))
                    columns 
                        i for ordinality
                    ,   p_rownum  int path 'i'
                    ,   p_field_n int path 'j'
                    ,   p_key     varchar2(30) path 'key'
                    ,   p_val     varchar2(30) path 'val'
           )
        /
        

        Comment by Sayan Malakshinov — November 27, 2020 @ 12:51 pm GMT Nov 27,2020 | Reply

    • Sayan,

      Thanks for the links and further examples of generating this type of output.

      Regards
      Jonathan Lewis

      Comment by Jonathan Lewis — November 27, 2020 @ 11:54 am GMT Nov 27,2020 | Reply

  2. One more version of the print_table that I’ve been using for some time, authored by Valentin Nikotin:

    set lin 250 pages 1000 verify off termout off echo off
    col 1 new_val 1
    col 2 new_val 2
    col 3 new_val 3
    col 4 new_val 4
    select 1,2,3,4 from dual where 1=0;
    col query_text new_val query_text
    col cols_rem new_val cols_rem
    col cols_shr new_val cols_shr
    col val_to_ign new_val val_to_ign
    select q'{&1}' as query_text,
           ''''||replace('&2', ',', ''',''')||'''' as cols_rem,
           ''''||replace('&3', ',', ''',''')||'''' as cols_shr,
           q'{&4}' as val_to_ign
    from dual;
    set termout on
    
    set serveroutput on
    declare
        type ttbl is table of varchar2(30);
        p_exclude_cols  ttbl := ttbl (&cols_rem);
        p_shrink60_cols ttbl := ttbl (&cols_shr);
        p_query         varchar2(30000) := q'{
    &query_text
    }';
        l_theCursor     integer default dbms_sql.open_cursor;
        l_columnValue   varchar2(4000);
        l_status        integer;
        l_descTbl       dbms_sql.desc_tab;
        l_colCnt        number;
    begin
    --    execute immediate q'{alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss'}';
        dbms_sql.parse(  l_theCursor,  p_query, dbms_sql.native );
        dbms_sql.describe_columns
        ( l_theCursor, l_colCnt, l_descTbl );
        for i in 1 .. l_colCnt loop
            dbms_sql.define_column
            (l_theCursor, i, l_columnValue, 4000);
        end loop;
        l_status := dbms_sql.execute(l_theCursor);
        while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
            for i in 1 .. l_colCnt loop
                if not l_descTbl(i).col_name member of p_exclude_cols then
                    dbms_sql.column_value ( l_theCursor, i, l_columnValue );
                    if l_descTbl(i).col_name member of p_shrink60_cols then
                       l_columnValue := substr(l_columnValue, 1, 60);
                    end if;
                    if nvl(l_columnValue, 'NULL') <> nvl('&val_to_ign', '341rqkfbehjbwjrkggbqxgkb') then 
                      dbms_output.put_line ( rpad( l_descTbl(i).col_name, 30 ) || ': ' || l_columnValue );
                    end if;
                end if;
            end loop;
            dbms_output.put_line( '-----------------' );
        end loop;
    end;
    /
    
    col 1 clear
    col 2 clear
    col 3 clear
    col 4 clear
    col query_text clear
    col cols_rem clear
    col cols_shr clear
    col val_to_ign clear
    undef 1 2 3 4 query_text cols_rem cols_shr val_to_ign
    set serveroutput off
     

    Comment by Timur Akhmadeev — November 26, 2020 @ 11:05 am GMT Nov 26,2020 | Reply

  3. […] a little cosmetic work on it), and then passes the resulting string to a verion of Tom Kyte’s print_table() routine which produces the following […]

    Pingback by Row sizes 3 | Oracle Scratchpad — November 26, 2020 @ 12:08 pm GMT Nov 26,2020 | Reply

  4. Hi,

    “each row in a table as a list of (column_name , value)”

    It can be very useful. A problem with compatibility is not to check a native feature exists and to lose time. In this case I notice I don’t really have to adapt print_table.

    psql (11.7 (Debian 11.7-0+deb10u1))
    Saisissez « help » pour l'aide.
    
    test=# create table t(c1 int, c2 int);
    CREATE TABLE
    test=# insert into t values(1,2),(3,4);
    INSERT 0 2
    test=# select * from t;
     c1 | c2
    ----+----
      1 |  2
      3 |  4
    (2 lignes)
    
    test=# \x
    Affichage étendu activé.
    test=# select * from t;
    -[ RECORD 1 ]
    c1 | 1
    c2 | 2
    -[ RECORD 2 ]
    c1 | 3
    c2 | 4
    

    Regards,

    Phil Florent

    Comment by Phil Florent — November 27, 2020 @ 4:14 pm GMT Nov 27,2020 | Reply

  5. […] few weeks ago Jonathan Lewis has published a note about Tom Kyte’s print_table – a small PL/SQL procedure to output each row in a table as a list of (column_name , value). […]

    Pingback by Yet another PRINT_TABLE - as a SQL Macro! — December 20, 2020 @ 9:57 pm GMT Dec 20,2020 | Reply

  6. Hi Jonathan,
    starting with 19.6 there is another easy way – to use a SQL macro: https://blog.sqlora.com/en/yet-another-print_table-as-a-sql-macro/

    CREATE OR REPLACE FUNCTION print_table(t DBMS_TF.TABLE_T ) 
    RETURN VARCHAR2 SQL_MACRO
    AS
     v_cols clob ;
     v_unpivot  clob ;
     v_str   varchar2(200);
     v_delimiter varchar2(9):= ',';
     v_name dbms_id;
     v_sql clob;
    BEGIN
      FOR I IN 1..t.column.count LOOP
        v_name := t.column(i).description.name;
        IF t.column(i).description.type = dbms_tf.type_varchar2 THEN 
          v_str := v_name;
        ELSIF t.column(i).description.type = dbms_tf.type_number THEN
          v_str := 'to_char('||v_name||') as '||v_name;
        ELSIF t.column(i).description.type = dbms_tf.type_date THEN
          v_str := 'to_char('||v_name||',''YYYY-MM-DD HH24:MI:SS'') as '||v_name;
        END IF;
        v_cols := v_cols || v_delimiter || v_str;
        v_unpivot := v_unpivot || v_delimiter || v_name;
      END LOOP;
      v_cols := LTRIM(v_cols,',');
      v_unpivot := LTRIM(v_unpivot,',');
      v_sql := 'SELECT col_name, nvl(col_value,''(NULL)'') as col_value '||
             'FROM   (SELECT '|| v_cols ||', 
                      ''-------------'' as "--------" from t )'||
             ' UNPIVOT include nulls (col_value for col_name 
                  in ('||v_unpivot||', "--------")  )';
      return v_sql;         
    END;
    /
     
    SQL&gt; select * from print_table(scott.dept);
     
    COL_NAME COL_VALUE                               
    -------- ----------------------------------------
    DEPTNO   10                                      
    DNAME    ACCOUNTING                              
    LOC      NEW YORK                                
    -------- -------------                           
    DEPTNO   20                                      
    DNAME    RESEARCH                                
    LOC      DALLAS                                  
    -------- -------------                           
    DEPTNO   30                                      
    DNAME    SALES                                   
    LOC      CHICAGO                                 
    -------- -------------                           
    DEPTNO   40                                      
    DNAME    OPERATIONS                              
    LOC      BOSTON                                  
    -------- -------------                           
     
    16 rows selected. 
    

    Best regards,
    Andrej Pashchenko

    Comment by Andrej Pashchenko — December 20, 2020 @ 10:05 pm GMT Dec 20,2020 | Reply

  7. […] a few column formats to make the output readable (though sometimes I can take advantage of the “print_table()” procedure that Tom Kyte published a long time ago.  It only takes a little time to get the code […]

    Pingback by Lock Types | Oracle Scratchpad — February 18, 2021 @ 10:21 am GMT Feb 18,2021 | Reply


RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by WordPress.com.