CREATE TABLE trace_config
(
send_to VARCHAR2 (1),
trace_on VARCHAR2 (1)
)
Table created.
INSERT INTO trace_config (send_to, trace_on)
VALUES ('T', 'N')
1 row(s) inserted.
COMMIT
Statement processed.
CREATE TABLE trace_table
(
message VARCHAR2 (4000),
callstack CLOB,
created_by VARCHAR2 (30),
created_at DATE
)
Table created.
CREATE OR REPLACE PACKAGE trace_mgr
IS
PROCEDURE to_screen;
PROCEDURE to_table;
PROCEDURE turn_on;
PROCEDURE turn_off;
FUNCTION sending_to_screen
RETURN BOOLEAN;
FUNCTION trace_is_on
RETURN BOOLEAN;
PROCEDURE put (msg_in IN VARCHAR2);
PROCEDURE put (boolean_in IN BOOLEAN);
END;
Package created.
CREATE OR REPLACE PACKAGE BODY trace_mgr
IS
PROCEDURE to_screen
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
UPDATE trace_config SET send_to = 'S';
COMMIT;
END;
PROCEDURE to_table
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
UPDATE trace_config SET send_to = 'T';
COMMIT;
END;
PROCEDURE turn_on
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
UPDATE trace_config SET trace_on = 'Y';
COMMIT;
END;
PROCEDURE turn_off
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
UPDATE trace_config SET trace_on = 'N';
COMMIT;
END;
FUNCTION trace_is_on
RETURN BOOLEAN
IS
l_trace trace_config.trace_on%TYPE;
BEGIN
SELECT trace_on INTO l_trace FROM trace_config;
RETURN l_trace = 'Y';
END;
FUNCTION sending_to_screen
RETURN BOOLEAN
IS
l_send_to trace_config.send_to%TYPE;
BEGIN
SELECT send_to INTO l_send_to FROM trace_config;
RETURN l_send_to = 'S';
END;
PROCEDURE put (msg_in IN VARCHAR2)
IS
FUNCTION fullmsg
RETURN VARCHAR2
IS
BEGIN
RETURN ' At: '
|| TO_CHAR (SYSDATE, 'YYYY-MON-DD HH24:MI:SS')
|| ' Msg: '
|| msg_in;
END;
BEGIN
IF trace_is_on
THEN
IF sending_to_screen
THEN
DBMS_OUTPUT.put_line (fullmsg);
ELSE
INSERT INTO trace_table (MESSAGE,
callstack,
created_by,
created_at)
VALUES (msg_in,
DBMS_UTILITY.format_call_stack,
USER,
SYSDATE);
END IF;
END IF;
END;
PROCEDURE put (boolean_in IN BOOLEAN)
IS
BEGIN
DBMS_OUTPUT.put_line (
CASE boolean_in
WHEN TRUE THEN 'TRUE'
WHEN FALSE THEN 'FALSE'
ELSE 'NULL'
END);
END;
END;
Package Body created.