一直以来，觉得mysql中使用\g参数改变输出结果集的显示方式非常好用，尤其是在命令行界面。但是oracle数据库没有这个功能，今天在搜索到tom大师的一篇博文时，发现大师用一个存储过程print_table实现了类似这样的功能。只是我们这些凡夫俗子不知道而已，特意整理在此，方便自己或有需要的人以后查看。
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;
/
如下测试所示：
sql> set serveroutput on size 99999;
sql> execute print_table('select * from v$session where sid=997');
saddr : 000000085fa35ca0
sid : 997
serial# : 1
audsid : 0
paddr : 000000085f6b7e70
user# : 0
username :
command : 0
ownerid : 2147483644
taddr :
lockwait :
status : active
server : dedicated
schema# : 0
schemaname : sys
osuser : oracle
process : 5036
machine : xxxx
port : 0
terminal : unknown
program : oracle@xxxxx (dbw0)
type : background
sql_address : 00
sql_hash_value : 0
sql_id :
sql_child_number : 0
prev_sql_addr : 00
prev_hash_value : 0
prev_sql_id :
prev_child_number : 0
plsql_entry_object_id :
plsql_entry_subprogram_id :
plsql_object_id :
plsql_subprogram_id :
module :
module_hash : 0
action :
action_hash : 0
client_info :
fixed_table_sequence : 0
row_wait_obj# : -1
row_wait_file# : 0
row_wait_block# : 0
row_wait_row# : 0
logon_time : 04-jul-2018 21:15:52
last_call_et : 5272838
pdml_enabled : no
failover_type : none
failover_method : none
failed_over : no
resource_consumer_group :
pdml_status : disabled
pddl_status : disabled
pq_status : disabled
current_queue_duration : 0
client_identifier :
blocking_session_status : no holder
blocking_instance :
blocking_session :
seq# : 34697
event# : 3
event : rdbms ipc message
p1text : timeout
p1 : 300
p1raw : 000000000000012c
p2text :
p2 : 0
p2raw : 00
p3text :
p3 : 0
p3raw : 00
wait_class_id : 2723168908
wait_class# : 6
wait_class : idle
wait_time : 0
seconds_in_wait : 107
state : waiting
service_name : sys$background
sql_trace : disabled
sql_trace_waits : false
sql_trace_binds : false
ecid :
-----------------
pl/sql procedure successfully completed.
sql>
参考资料:
https://asktom.oracle.com/pls/asktom/f?p=100:11:::::p11_question_id:1035431863958,%7bprint_table%7d