Oracle DBA, How To, Error, Cause and Action

Print Table Vertically

Following Procedure was written by Thomas Kyte (a Vice President in Oracle Corporation, and the Tom behind the popular web forum asktom)

Allow to print table vertically..

create or replace procedure print_table ( p_query in varchar2,
p_date_fmt in varchar2 default 'dd-mon-yyyy hh24:mi:ss' )




-- this utility is designed to be installed ONCE in a database and used
-- by all. Also, it is nice to have roles enabled so that queries by
-- DBA's that use a role to gain access to the DBA_* views still work
-- that is the purpose of AUTHID CURRENT_USER
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_cs varchar2(255);
l_date_fmt varchar2(255);




-- small inline procedure to restore the sessions state
-- we may have modified the cursor sharing and nls date format
-- session variables, this just restores them
procedure restore
is
begin
if ( upper(l_cs) not in ( 'FORCE','SIMILAR' )) then
  execute immediate
  'alter session set cursor_sharing=exact';
end if;


if ( p_date_fmt is not null ) then
  execute immediate
  'alter session set nls_date_format=''' || l_date_fmt || '''';
end if;


dbms_sql.close_cursor(l_theCursor);
end restore;


begin
-- I like to see the dates print out with times, by default, the
-- format mask I use includes that. In order to be "friendly"
-- we save the date current sessions date format and then use
-- the one with the date and time. Passing in NULL will cause
-- this routine just to use the current date format
if ( p_date_fmt is not null ) then
  select sys_context( 'userenv', 'nls_date_format' ) into l_date_fmt
  from dual;


execute immediate
  'alter session set nls_date_format=''' || p_date_fmt || '''';
end if;




-- to be bind variable friendly on this ad-hoc queries, we
-- look to see if cursor sharing is already set to FORCE or
-- similar, if not, set it so when we parse -- literals
-- are replaced with binds
if ( dbms_utility.get_parameter_value ( 'cursor_sharing', l_status, l_cs ) = 1 ) then
  if ( upper(l_cs) not in ('FORCE','SIMILAR')) then
    execute immediate
    'alter session set cursor_sharing=force';
  end if;
end if;




-- parse and describe the query sent to us. we need
-- to know the number of columns and their names.
dbms_sql.parse( l_theCursor, p_query, dbms_sql.native );
dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );




-- define all columns to be cast to varchar2's, we
-- are just printing them out
for i in 1 .. l_colCnt loop
  if ( l_descTbl(i).col_type not in ( 113 ) ) then
    dbms_sql.define_column (l_theCursor, i, l_columnValue, 4000);
  end if;
end loop;


-- execute the query, so we can fetch
l_status := dbms_sql.execute(l_theCursor);




-- loop and print out each column on a separate line
-- bear in mind that dbms_output only prints 255 characters/line
-- so we'll only see the first 200 characters by my design...


while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
  for i in 1 .. l_colCnt loop
    if ( l_descTbl(i).col_type not in ( 113 ) ) then
      dbms_sql.column_value( l_theCursor, i, l_columnValue );
      dbms_output.put_line( rpad( l_descTbl(i).col_name, 30 ) || ': ' ||
      substr( l_columnValue, 1, 200 ) );
    end if;
  end loop;
  dbms_output.put_line( '-----------------' );
end loop;


-- now, restore the session state, no matter what
restore;
exception
when others then
restore;
raise;
end;
/


grant execute on print_table to public;


create public synonym print_table for sys.print_table;


Usage

SQL > set serveroutput on size unlimited



SQL> exec print_Table('select * from dba_objects where rownum = 1');
OWNER                         : SYS
OBJECT_NAME                   : ICOL$
SUBOBJECT_NAME                :
OBJECT_ID                     : 20
DATA_OBJECT_ID                : 2
OBJECT_TYPE                   : TABLE
CREATED                       : 29-sep-2010 16:35:49
LAST_DDL_TIME                 : 29-sep-2010 17:09:31
TIMESTAMP                     : 2010-09-29:16:35:49
STATUS                        : VALID
TEMPORARY                     : N
GENERATED                     : N
SECONDARY                     : N
NAMESPACE                     : 1
EDITION_NAME                  :
-----------------


PL/SQL procedure successfully completed.