Formatting Query Results

--- The Print Table Procedure from Tom Kyte:
--- Reference: https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1035431863958

Using this you can have the SPOOL vertically instead of the standard way... 

1.) Compile the procedure:

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;
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.put_line( '-----------------' );
end loop;
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;
/

2.) Example to use:

[oracle@hqsdboitt01bt / SID : kdbpp]$ sql

SQL*Plus: Release 11.2.0.4.0 Production on Fri Nov 18 12:02:43 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning option

SYS @  > set serveroutput on
SYS @  > exec print_table('select * from v$instance');
INSTANCE_NUMBER               : 1
INSTANCE_NAME                 : kdbpp
HOST_NAME                     : hqsdboitt01bt.kclx.kneip.com
VERSION                       : 11.2.0.4.0
STARTUP_TIME                  : 16-nov-2016 18:28:11
STATUS                        : OPEN
PARALLEL                      : NO
THREAD#                       : 1
ARCHIVER                      : STOPPED
LOG_SWITCH_WAIT               :
LOGINS                        : ALLOWED
SHUTDOWN_PENDING              : NO
DATABASE_STATUS               : ACTIVE
INSTANCE_ROLE                 : PRIMARY_INSTANCE
ACTIVE_STATE                  : NORMAL
BLOCKED                       : NO
-----------------

PL/SQL procedure successfully completed.
Advertisements

Leave a Reply

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s