Super Simple Execution Tracing Package

Statement1

CREATE TABLE trace_config
(
   send_to    VARCHAR2 (1),
   trace_on   VARCHAR2 (1)
)

Table created.

Statement2

INSERT INTO trace_config (send_to, trace_on)
     VALUES ('T', 'N')

1 row(s) inserted.

Statement3

COMMIT

Statement processed.

Statement4

CREATE TABLE trace_table
(
   message      VARCHAR2 (4000),
   callstack    CLOB,
   created_by   VARCHAR2 (30),
   created_at   DATE
)

Table created.

Statement5

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.

Statement6

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.