The store procedure takes 2 parameters:
- SQL statement to be executed
- Date format to printed.
CREATE OR REPLACE PROCEDURE print_table (
p_query IN VARCHAR2,
p_date_fmt IN VARCHAR2 DEFAULT 'dd-mon-yyyy hh24:mi:ss'
)
-- this utility is designed to be installed ONCE in a database and used
-- by all. Also, it is nice to have roles enabled so that queries by
-- DBA's that use a role to gain access to the DBA_* views still work
-- that is the purpose of AUTHID CURRENT_USER
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;
l_cs VARCHAR2(255);
l_date_fmt VARCHAR2(255);
-- small inline procedure to restore the sessions state
-- we may have modified the cursor sharing and nls date format
-- session variables, this just restores them
PROCEDURE Restore
IS
BEGIN
IF ( Upper(l_cs) NOT IN ( 'FORCE', 'SIMILAR' ) ) THEN
EXECUTE IMMEDIATE 'alter session set cursor_sharing=exact';
END IF;
IF ( p_date_fmt IS NOT NULL ) THEN
EXECUTE IMMEDIATE 'alter session set nls_date_format='''|| l_date_fmt||
'''';
END IF;
dbms_sql.Close_cursor(l_thecursor);
END restore;
BEGIN
-- I like to see the dates print out with times, by default, the
-- format mask I use includes that. In order to be "friendly"
-- we save the date current sessions date format and then use
-- the one with the date and time. Passing in NULL will cause
-- this routine just to use the current date format
IF ( p_date_fmt IS NOT NULL ) THEN
SELECT Sys_context('userenv', 'nls_date_format')
INTO l_date_fmt
FROM dual;
EXECUTE IMMEDIATE 'alter session set nls_date_format='''|| p_date_fmt||
'''';
END IF;
-- to be bind variable friendly on this ad-hoc queries, we
-- look to see if cursor sharing is already set to FORCE or
-- similar, if not, set it so when we parse -- literals
-- are replaced with binds
IF ( dbms_utility.Get_parameter_value ('cursor_sharing', l_status, l_cs) = 1
)
THEN
IF ( Upper(l_cs) NOT IN ( 'FORCE', 'SIMILAR' ) ) THEN
EXECUTE IMMEDIATE 'alter session set cursor_sharing=force';
END IF;
END IF;
-- parse and describe the query sent to us. we need
-- to know the number of columns and their names.
dbms_sql.Parse(l_thecursor, p_query, dbms_sql.native);
dbms_sql.Describe_columns (l_thecursor, l_colcnt, l_desctbl);
-- define all columns to be cast to varchar2's, we
-- are just printing them out
FOR i IN 1 .. l_colcnt LOOP
IF ( L_desctbl(i).col_type NOT IN ( 113 ) ) THEN
dbms_sql.Define_column (l_thecursor, i, l_columnvalue, 4000);
END IF;
END LOOP;
-- execute the query, so we can fetch
l_status := dbms_sql.EXECUTE(l_thecursor);
-- loop and print out each column on a separate line
-- bear in mind that dbms_output only prints 255 characters/line
-- so we'll only see the first 200 characters by my design...
WHILE ( dbms_sql.Fetch_rows(l_thecursor) > 0 ) LOOP
FOR i IN 1 .. l_colcnt LOOP
IF ( L_desctbl(i).col_type NOT IN ( 113 ) ) THEN
dbms_sql.Column_value (l_thecursor, i, l_columnvalue);
dbms_output.Put_line (Rpad(L_desctbl(i).col_name, 30)
|| ': '
|| Substr(l_columnvalue, 1, 200));
END IF;
END LOOP;
dbms_output.Put_line('-----------------');
END LOOP;
-- now, restore the session state, no matter what
restore;
EXCEPTION
WHEN OTHERS THEN
restore;
-- RAISE;
END;
/
See an example below:
set serveroutput on
exec print_table('select * from dba_tables where owner=''SYS'' and table_name=''AUD$''');
OWNER : SYS
TABLE_NAME : AUD$
TABLESPACE_NAME : SYSTEM
CLUSTER_NAME :
IOT_NAME :
STATUS : VALID
PCT_FREE : 10
PCT_USED : 40
INI_TRANS : 1
MAX_TRANS : 255
INITIAL_EXTENT : 65536
NEXT_EXTENT : 1048576
MIN_EXTENTS : 1
MAX_EXTENTS : 2147483645
PCT_INCREASE :
FREELISTS : 1
FREELIST_GROUPS : 1
LOGGING : YES
BACKED_UP : N
NUM_ROWS : 368
BLOCKS : 12
EMPTY_BLOCKS : 0
AVG_SPACE : 0
CHAIN_CNT : 0
AVG_ROW_LEN : 162
AVG_SPACE_FREELIST_BLOCKS : 0
NUM_FREELIST_BLOCKS : 0
DEGREE : 1
INSTANCES : 1
CACHE : N
TABLE_LOCK : ENABLED
SAMPLE_SIZE : 368
LAST_ANALYZED : 07-jun-2019 21:33:31
PARTITIONED : NO
IOT_TYPE :
TEMPORARY : N
SECONDARY : N
NESTED : NO
BUFFER_POOL : DEFAULT
FLASH_CACHE : DEFAULT
CELL_FLASH_CACHE : DEFAULT
ROW_MOVEMENT : DISABLED
GLOBAL_STATS : YES
USER_STATS : NO
DURATION :
SKIP_CORRUPT : DISABLED
MONITORING : YES
CLUSTER_OWNER :
DEPENDENCIES : DISABLED
COMPRESSION : DISABLED
COMPRESS_FOR :
DROPPED : NO
READ_ONLY : NO
SEGMENT_CREATED : YES
RESULT_CACHE : DEFAULT
-----------------
