129 lines
4.7 KiB
Plaintext
129 lines
4.7 KiB
Plaintext
CREATE OR REPLACE PACKAGE dsp AUTHID CURRENT_USER AS
|
|
-- --------------------------------------------------------------------------
|
|
-- Name : https://oracle-base.com/dba/miscellaneous/dsp.pks
|
|
-- Author : Tim Hall
|
|
-- Description : An extension of the DBMS_OUTPUT package.
|
|
-- Requirements : https://oracle-base.com/dba/miscellaneous/dsp.pkb
|
|
-- Ammedments :
|
|
-- When Who What
|
|
-- =========== ======== =================================================
|
|
-- 08-JAN-2002 Tim Hall Initial Creation
|
|
-- 04-APR-2005 Tim Hall Store last call. Add get_last_prefix and
|
|
-- get_last_data to allow retrieval.
|
|
-- Switch from date to timestamp for greater accuracy.
|
|
-- 02-MAR-2013 Tim Hall Added file_contents pipelined table function.
|
|
-- Added delete_file.
|
|
-- Added example usage comments.
|
|
-- 02-DEC-2013 Tim Hall Add p_trace_level parameter to most code to
|
|
-- limit amount of trace produced.
|
|
-- Added "AUTHID CURRENT_USER".
|
|
-- 21-NOV-2018 Tim Hall Add CLOB overloads to LINE.
|
|
-- --------------------------------------------------------------------------
|
|
-- Example usage :
|
|
/*
|
|
-- Set up test directory object.
|
|
CREATE OR REPLACE DIRECTORY test_dir AS '/home/oracle/';
|
|
GRANT READ, WRITE ON DIRECTORY test_dir TO test;
|
|
|
|
-- Test DSP.
|
|
BEGIN
|
|
-- Turn on tracing, redirecting it to a file.
|
|
DSP.show_output_on;
|
|
DSP.file_output_on('TEST_DIR', 'test.txt');
|
|
DSP.show_date_on;
|
|
DSP.line_wrap_on;
|
|
DSP.set_max_width(200);
|
|
|
|
-- Trace something.
|
|
DSP.line('This is a test');
|
|
END;
|
|
|
|
-- Check the contents of the file.
|
|
SELECT * FROM TABLE(DSP.file_contents('TEST_DIR', 'test.txt'));
|
|
|
|
-- Delete the file.
|
|
EXEC DSP.delete_file('TEST_DIR', 'test.txt');
|
|
*/
|
|
-- --------------------------------------------------------------------------
|
|
|
|
-- Constants to control tracing level.
|
|
trace_level_all CONSTANT PLS_INTEGER := 99;
|
|
trace_level_info CONSTANT PLS_INTEGER := 30;
|
|
trace_level_warn CONSTANT PLS_INTEGER := 20;
|
|
trace_level_error CONSTANT PLS_INTEGER := 10;
|
|
|
|
PROCEDURE reset_defaults;
|
|
|
|
PROCEDURE show_output_on(p_trace_level IN PLS_INTEGER := DSP.trace_level_all);
|
|
PROCEDURE show_output_off;
|
|
|
|
PROCEDURE show_date_on;
|
|
PROCEDURE show_date_off;
|
|
|
|
PROCEDURE line_wrap_on;
|
|
PROCEDURE line_wrap_off;
|
|
|
|
PROCEDURE set_max_width (p_width IN PLS_INTEGER);
|
|
|
|
PROCEDURE set_date_format (p_date_format IN VARCHAR2);
|
|
|
|
PROCEDURE file_output_on (p_file_dir IN VARCHAR2 DEFAULT NULL,
|
|
p_file_name IN VARCHAR2 DEFAULT NULL);
|
|
|
|
PROCEDURE file_output_off;
|
|
|
|
FUNCTION get_last_prefix
|
|
RETURN VARCHAR2;
|
|
|
|
FUNCTION get_last_data
|
|
RETURN VARCHAR2;
|
|
|
|
PROCEDURE line (p_data IN VARCHAR2,
|
|
p_trace_level IN PLS_INTEGER := DSP.trace_level_info);
|
|
|
|
PROCEDURE line (p_data IN CLOB,
|
|
p_trace_level IN PLS_INTEGER := DSP.trace_level_info);
|
|
|
|
PROCEDURE line (p_data IN NUMBER,
|
|
p_trace_level IN PLS_INTEGER := DSP.trace_level_info);
|
|
|
|
PROCEDURE line (p_data IN BOOLEAN,
|
|
p_trace_level IN PLS_INTEGER := DSP.trace_level_info);
|
|
|
|
PROCEDURE line (p_data IN DATE,
|
|
p_format IN VARCHAR2 DEFAULT 'DD-MON-YYYY HH24:MI:SS.FF',
|
|
p_trace_level IN PLS_INTEGER := DSP.trace_level_info);
|
|
|
|
PROCEDURE line (p_prefix IN VARCHAR2,
|
|
p_data IN VARCHAR2,
|
|
p_trace_level IN PLS_INTEGER := DSP.trace_level_info);
|
|
|
|
PROCEDURE line (p_prefix IN VARCHAR2,
|
|
p_data IN CLOB,
|
|
p_trace_level IN PLS_INTEGER := DSP.trace_level_info);
|
|
|
|
PROCEDURE line (p_prefix IN VARCHAR2,
|
|
p_data IN NUMBER,
|
|
p_trace_level IN PLS_INTEGER := DSP.trace_level_info);
|
|
|
|
PROCEDURE line (p_prefix IN VARCHAR2,
|
|
p_data IN BOOLEAN,
|
|
p_trace_level IN PLS_INTEGER := DSP.trace_level_info);
|
|
|
|
PROCEDURE line (p_prefix IN VARCHAR2,
|
|
p_data IN DATE,
|
|
p_format IN VARCHAR2 DEFAULT 'DD-MON-YYYY HH24:MI:SS.FF',
|
|
p_trace_level IN PLS_INTEGER := DSP.trace_level_info);
|
|
|
|
FUNCTION file_contents (p_dir IN VARCHAR2,
|
|
p_file IN VARCHAR2)
|
|
RETURN DBMSOUTPUT_LINESARRAY PIPELINED;
|
|
|
|
PROCEDURE delete_file (p_dir IN VARCHAR2,
|
|
p_file IN VARCHAR2);
|
|
|
|
END dsp;
|
|
/
|
|
|
|
SHOW ERRORS
|