Skip to Main Content
  • Questions

  • printing data vertically like PRINT_TABLE via sqlplus

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, José Laurindo.

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

Last updated: August 14, 2025 - 1:06 pm UTC

Version: 10.2.0 / 11.2.0

Viewed 10K+ times! This question is

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 Connor said...

I use
set verify off
declare
    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 )
    is
    BEGIN
        execute_immediate p_sql;
    END;
    procedure p ( p_str in varchar2 )
    is
        l_str   long := p_str;
    begin
        loop
            exit when l_str is null;
            dbms_output.put_line( substr( l_str, 1, 250 ) );
            l_str := substr( l_str, 251 );
        end loop;
    end;
begin
    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'' ');
exception
    when others then
        execute_immediate( 'alter session set
                         nls_date_format=''dd-MON-yy'' ');
        raise;
end;
/
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.

Rating

  (3 ratings)

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

Comments

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

Regards,

J. Laurindo Chiappa

Cursor not closed

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

Tom:

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'|');
 dbms_sql.close_cursor(l_cursor);

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

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

Much simpler solution in more recent versions

Stew Ashton, August 14, 2025 - 10:00 am UTC

I don't know why this question got bumped up, but I suggest that starting with version 19c we should get used to reading data in JSON format. As a bonus, this will handle JSON columns gracefully.
set pagesize 999
select json_serialize(json_object(*) pretty error on error) data from
(select e.*, json_object(empno, ename) json_col from emp e where rownum <= 2);

DATA 
----------------
{
  "EMPNO" : 7839,
  "ENAME" : "KING",
  "JOB" : "PRESIDENT",
  "MGR" : null,
  "HIREDATE" : "1981-11-17T00:00:00",
  "SAL" : 5000,
  "COMM" : null,
  "DEPTNO" : 10,
  "JSON_COL" :
  {
    "empno" : 7839,
    "ename" : "KING"
  }
}

{
  "EMPNO" : 7698,
  "ENAME" : "BLAKE",
  "JOB" : "MANAGER",
  "MGR" : 7839,
  "HIREDATE" : "1981-05-01T00:00:00",
  "SAL" : 2850,
  "COMM" : null,
  "DEPTNO" : 30,
  "JSON_COL" :
  {
    "empno" : 7698,
    "ename" : "BLAKE"
  }
}

Chris Saxon
August 14, 2025 - 1:06 pm UTC

Thanks for the suggestion Stew
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

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