Skip to Main Content
  • Questions
  • printing data vertically like PRINT_TABLE via sqlplus


Question and Answer

Tom Kyte

Thanks for the question, José Laurindo.

Asked: May 07, 2012 - 3:23 pm UTC

Last updated: May 10, 2012 - 3:35 am UTC

Version: 10.2.0 / 11.2.0

Viewed 1000+ times

You Asked

In some 10gr2 and 11gr2 Customer´s databases I need, inside sqlplus , print some data vertically, much like your PRINT_TABLE function, but my user is "read-only" , I´m not allowed to create nothing (ie, no stored PL/SQLs, no tables, nothing permanent) inside this databases, what invalidate the option for your PRINT_TABLE function ...
Would you have some suggestion to mimic PRINT_TABLE functionality but without using any permanent db object, maybe inside a sqlplus script, or something like that ?

Best regards,

J. Laurindo Chiappa
Oracle DBA

and Tom said...

I use
set verify off
    l_theCursor     integer default dbms_sql.open_cursor;
    l_columnValue   varchar2(4000);
    l_status        integer;
    l_descTbl       dbms_sql.desc_tab;
    l_colCnt        number;

    procedure execute_immediate( p_sql in varchar2 )
        l_status := dbms_sql.execute(l_theCursor);
    procedure p ( p_str in varchar2 )
        l_str   long := p_str;
            exit when l_str is null;
            dbms_output.put_line( substr( l_str, 1, 250 ) );
            l_str := substr( l_str, 251 );
        end loop;
    execute_immediate( 'alter session set nls_date_format=
                        ''dd-mon-yyyy hh24:mi:ss'' ');
    dbms_sql.parse(  l_theCursor,
                     replace( '&1', '"', ''''),
                     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 );
            p( rpad( l_descTbl(i).col_name,
                  30 ) || ': "' || l_columnValue || '"' );
        end loop;
        dbms_output.put_line( '-----------------' );
    end loop;
    execute_immediate( 'alter session set nls_date_format=
                           ''dd-MON-yy'' ');
    when others then
        execute_immediate( 'alter session set
                         nls_date_format=''dd-MON-yy'' ');
set verify on

that script in a pinch. Use double quotes to quote literals:

ops$tkyte%ORA11GR2> @printtbl 'select * from scott.emp where ename = "KING"'
EMPNO                         : "7839"
ENAME                         : "KING"
JOB                           : "PRESIDENT"
MGR                           : ""
HIREDATE                      : "17-nov-1981 00:00:00"
SAL                           : "5000"
COMM                          : ""
DEPTNO                        : "10"

PL/SQL procedure successfully completed.


  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment


DBMS_OUTPUT, of course...

José Laurindo Chiappa, May 08, 2012 - 12:56 pm UTC

Yes, send the data read to the screen via DBMS_OUTPUT was in my thoughts : and given the UNLIMITED size for output buffer in sqlplus introduced in 10g, I must not get any size problem even for large queries, so this must be the solution, yes...


J. Laurindo Chiappa

Cursor not closed

Rajeshwaran Jeyabal, May 09, 2012 - 8:42 am UTC


Is there is any specific reason that you don't close cursor in that previous example?

 execute_immediate(q'|alter session set nls_date_format='dd-mon-yy'|');

  when others then
   if dbms_sql.is_open(l_cursor) then
   end if;
   dbms_output.put_line ( dbms_utility.format_error_backtrace);
   execute_immediate(q'|alter session set nls_date_format='dd-mon-yy'|');

Tom Kyte
May 10, 2012 - 3:35 am UTC

that is what they call "an oversight" also known as "a bug"

It just never hit me before - it has a cursor leak

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library