print_table 实现 sqlplus 类似 mysql \G 及 psql \x 的功能

使用 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=''yyyy-mm-dd 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;
SQL> exec print_table('select * from v$database');
DBID			      		  : 1574058918
NAME			     		  : ORCL
CREATED 		      		  : 2020-07-20 16:49:42
RESETLOGS_CHANGE#	      	  : 1594143
RESETLOGS_TIME		      	  : 2020-07-20 16:49:44
PRIOR_RESETLOGS_CHANGE#       : 1
PRIOR_RESETLOGS_TIME	      : 2014-07-07 05:38:47
LOG_MODE		       		  : ARCHIVELOG
CHECKPOINT_CHANGE#	     	  : 1857173
ARCHIVE_CHANGE# 	      	  : 1845186
CONTROLFILE_TYPE	      	  : CURRENT
CONTROLFILE_CREATED	   	      : 2020-07-20 16:49:42
CONTROLFILE_SEQUENCE#	      : 2670
CONTROLFILE_CHANGE#	      	  : 1878062
CONTROLFILE_TIME	      	  : 2020-07-21 15:39:34
OPEN_RESETLOGS		      	  : NOT ALLOWED
VERSION_TIME		     	  : 2020-07-20 16:49:42
OPEN_MODE		     		  : READ WRITE
PROTECTION_MODE 	      	  : MAXIMUM PERFORMANCE
PROTECTION_LEVEL	     	  : MAXIMUM PERFORMANCE
REMOTE_ARCHIVE		    	  : ENABLED
ACTIVATION#		     	      : 1574036646
SWITCHOVER#		      		  : 1574036646
DATABASE_ROLE		      	  : PRIMARY
ARCHIVELOG_CHANGE#	     	  : 1857173
ARCHIVELOG_COMPRESSION	      : DISABLED
SWITCHOVER_STATUS	      	  : TO STANDBY
DATAGUARD_BROKER	      	  : DISABLED
GUARD_STATUS		      	  : NONE
SUPPLEMENTAL_LOG_DATA_MIN     : NO
SUPPLEMENTAL_LOG_DATA_PK      : NO
SUPPLEMENTAL_LOG_DATA_UI      : NO
FORCE_LOGGING		     	  : YES
PLATFORM_ID		     	      : 13
PLATFORM_NAME		     	  : Linux x86 64-bit
RECOVERY_TARGET_INCARNATION#  : 2
LAST_OPEN_INCARNATION#	      : 2
CURRENT_SCN		     		  : 1878113
FLASHBACK_ON		     	  : YES
SUPPLEMENTAL_LOG_DATA_FK      : NO
SUPPLEMENTAL_LOG_DATA_ALL     : NO
DB_UNIQUE_NAME		     	  : orclp
STANDBY_BECAME_PRIMARY_SCN    : 0
FS_FAILOVER_STATUS	     	  : DISABLED
FS_FAILOVER_CURRENT_TARGET    :
FS_FAILOVER_THRESHOLD	      : 0
FS_FAILOVER_OBSERVER_PRESENT  :
FS_FAILOVER_OBSERVER_HOST     :
CONTROLFILE_CONVERTED	      : NO
PRIMARY_DB_UNIQUE_NAME	      :
SUPPLEMENTAL_LOG_DATA_PL      : NO
MIN_REQUIRED_CAPTURE_CHANGE#  :
CDB			     			  : YES
CON_ID			     		  : 0
PENDING_ROLE_CHANGE_TASKS     : NONE
CON_DBID		     		  : 1574058918
FORCE_FULL_DB_CACHING	      : NO
-----------------

PL/SQL procedure successfully completed.

参考:
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1035431863958