Display data from a select statement vertically
SQL> @v 'select * from v$session where sid = 299' : n
1 = select statement e.g. 'select * from dual'
2 = divider, either TAB or whatever is passed
3 = ignore nulls, either Y or N
select * from v$session where sid = 299
---------------------------------------------------------------
SADDR :0000000119928160
SID :299
SERIAL# :3005
AUDSID :6399125
PADDR :000000011963F5C0
USER# :642
USERNAME :DEV_TOOLS
COMMAND :0
OWNERID :2147483644
TADDR :
LOCKWAIT :
STATUS :SNIPED
SERVER :DEDICATED
SCHEMA# :642
SCHEMANAME :DEV_TOOLS
OSUSER :jim
PROCESS :7188:6244
MACHINE :XX-1956-GGH01
PORT :50228
TERMINAL :XX-1956-GGH01
PROGRAM :frmweb.exe
TYPE :USER
SQL_ADDRESS :00
SQL_HASH_VALUE :0
SQL_ID :
SQL_CHILD_NUMBER :
SQL_EXEC_START :
SQL_EXEC_ID :
PREV_SQL_ADDR :00000001041889C0
PREV_HASH_VALUE :3906274763
PREV_SQL_ID :8q9f2u7nda1fb
PREV_CHILD_NUMBER :0
PREV_EXEC_START :30-sep-2015 11:17:33
PREV_EXEC_ID :16777602
PLSQL_ENTRY_OBJECT_ID :
PLSQL_ENTRY_SUBPROGRAM_ID :
PLSQL_OBJECT_ID :
PLSQL_SUBPROGRAM_ID :
MODULE :frmweb.exe
MODULE_HASH :854945150
ACTION :
ACTION_HASH :0
CLIENT_INFO :
FIXED_TABLE_SEQUENCE :8653041
ROW_WAIT_OBJ# :-1
ROW_WAIT_FILE# :0
ROW_WAIT_BLOCK# :0
ROW_WAIT_ROW# :0
TOP_LEVEL_CALL# :94
LOGON_TIME :30-sep-2015 11:04:10
LAST_CALL_ET :13493
PDML_ENABLED :NO
FAILOVER_TYPE :NONE
FAILOVER_METHOD :NONE
FAILED_OVER :NO
RESOURCE_CONSUMER_GROUP :
PDML_STATUS :DISABLED
PDDL_STATUS :ENABLED
PQ_STATUS :ENABLED
CURRENT_QUEUE_DURATION :0
CLIENT_IDENTIFIER :
BLOCKING_SESSION_STATUS :NO HOLDER
BLOCKING_INSTANCE :
BLOCKING_SESSION :
FINAL_BLOCKING_SESSION_STATUS :NO HOLDER
FINAL_BLOCKING_INSTANCE :
FINAL_BLOCKING_SESSION :
SEQ# :259
EVENT# :352
EVENT :SQL*Net message from client
P1TEXT :driver id
P1 :1413697536
P1RAW :0000000054435000
P2TEXT :#bytes
P2 :1
P2RAW :0000000000000001
P3TEXT :
P3 :0
P3RAW :00
WAIT_CLASS_ID :2723168908
WAIT_CLASS# :6
WAIT_CLASS :Idle
WAIT_TIME :0
SECONDS_IN_WAIT :13493
STATE :WAITING
WAIT_TIME_MICRO :13493171864
TIME_REMAINING_MICRO :-1
TIME_SINCE_LAST_WAIT_MICRO :0
SERVICE_NAME :OQDEV_APP
SQL_TRACE :DISABLED
SQL_TRACE_WAITS :FALSE
SQL_TRACE_BINDS :FALSE
SQL_TRACE_PLAN_STATS :FIRST EXEC
SESSION_EDITION_ID :100
CREATOR_ADDR :000000011963F5C0
CREATOR_SERIAL# :19
ECID :
---------------------------------------------------------------
1 row(s) returned
PL/SQL procedure successfully completed.
prompt
spool v.rpt
-- based on Tom Kyte's print_table procedure
prompt 1 = select statement e.g. 'select * from dual'
prompt
prompt 2 = divider, either TAB or whatever is passed
prompt
prompt 3 = ignore nulls, either Y or N
prompt
declare
ls_select varchar2( 32000 );
procedure print_table( p_query in varchar2 )
is
li_status integer;
li_thecursor integer default dbms_sql.open_cursor;
ln_colcount number;
ln_rowcount number := 0;
ls_columnvalue varchar2(4000);
ls_divider varchar2(10);
lt_desctbl dbms_sql.desc_tab;
begin
if upper( '&2' ) = 'TAB' then
ls_divider := chr(9);
elsif upper( '&2' ) in ( 'CHR(10)', 'LF', 'CR' ) then
ls_divider := chr(10);
else
ls_divider := '&2';
end if;
execute immediate 'alter session set nls_date_format=''dd-mon-yyyy hh24:mi:ss'' ';
dbms_sql.parse( li_thecursor, p_query, dbms_sql.native );
dbms_sql.describe_columns( li_thecursor, ln_colcount, lt_desctbl );
for i in 1 .. ln_colcount loop
dbms_sql.define_column(li_thecursor, i, ls_columnvalue, 4000);
end loop;
li_status := dbms_sql.execute(li_thecursor);
dbms_output.put_line( '---------------------------------------------------------------' );
while ( dbms_sql.fetch_rows(li_thecursor) > 0 ) loop
ln_rowcount := ln_rowcount + 1;
for i in 1 .. ln_colcount loop
dbms_sql.column_value( li_thecursor, i, ls_columnvalue );
if substr( upper( '&3' ), 1, 1 ) = 'N' or length( nvl( trim( ls_columnvalue ), '' ) ) > 0 then
dbms_output.put_line( rpad( lt_desctbl(i).col_name, 30 ) || ls_divider || ls_columnvalue );
end if;
end loop;
dbms_output.put_line( '---------------------------------------------------------------' );
end loop;
dbms_output.put_line( ln_rowcount || ' row(s) returned' );
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'' ';
dbms_output.put_line( '' );
dbms_output.put_line( '' );
dbms_output.put_line( 'select statement must be enclosed in single quotation marks' );
dbms_output.put_line( '' );
dbms_output.put_line( '' );
dbms_output.put_line( '' );
raise;
end;
begin
ls_select := '';
for i in 1..length( '&1' ) loop
if substr( '&1', i, 1 ) = '~' then
ls_select := ls_select || '''';
else
ls_select := ls_select || substr( '&1', i, 1 );
end if;
end loop;
dbms_output.put_line( ls_select );
print_table( ls_select );
end;
/
spool off