oracle中print_table存储过程实例介绍

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,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;
/

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 scheR_482_11845@A# : 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_C@R_673_10636@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>