Compile the Generator Utility

CREATE OR REPLACE PROCEDURE gen_logger_param_trace ( 
   pkg_or_prog_in   IN VARCHAR2, 
   pkg_subprog_in   IN VARCHAR2 DEFAULT NULL) 
/* 
   Overview: generates trace code for all parameters in your subprogram. 
 
   Author: Steven Feuerstein, steven.feuerstein@oracle.com 
*/ 
IS 
   c_prog_name   CONSTANT VARCHAR2 (200) 
      :=    CASE 
               WHEN pkg_subprog_in IS NOT NULL THEN pkg_or_prog_in || '.' 
            END 
         || pkg_subprog_in ; 
 
   CURSOR arg_info_cur ( 
      prog_in      IN VARCHAR2, 
      subprog_in   IN VARCHAR2 DEFAULT NULL) 
   IS 
        SELECT NVL (argument_name, 'RETURN_VALUE') argument_name, 
               data_type, 
               in_out 
          FROM user_arguments 
         WHERE     (   (package_name = prog_in AND subprog_in IS NULL) 
                    OR (package_name IS NULL AND object_name = prog_in) 
                    OR (package_name = prog_in AND object_name = subprog_in)) 
               AND data_level = 0 
      ORDER BY POSITION; 
 
   TYPE arg_info_aat IS TABLE OF arg_info_cur%ROWTYPE 
      INDEX BY PLS_INTEGER; 
 
   l_in_args              arg_info_aat; 
   l_out_args             arg_info_aat; 
 
   PROCEDURE get_argument_info (in_args    IN OUT arg_info_aat, 
                                out_args   IN OUT arg_info_aat) 
   IS 
      l_arg_info   arg_info_aat; 
   BEGIN 
      OPEN arg_info_cur (pkg_or_prog_in, pkg_subprog_in); 
 
      FETCH arg_info_cur BULK COLLECT INTO l_arg_info; 
 
      FOR indx IN 1 .. l_arg_info.COUNT 
      LOOP 
         IF l_arg_info (indx).in_out IN ('IN', 'IN/OUT') 
         THEN 
            in_args (in_args.COUNT + 1) := l_arg_info (indx); 
         END IF; 
 
         IF l_arg_info (indx).in_out IN ('OUT', 'IN/OUT') 
         THEN 
            out_args (out_args.COUNT + 1) := l_arg_info (indx); 
         END IF; 
      END LOOP; 
   END get_argument_info; 
 
   FUNCTION trace_prog_name (arg_type_in IN VARCHAR2) 
      RETURN VARCHAR2 
   IS 
   BEGIN 
      RETURN 'trace_' || arg_type_in || '_arguments'; 
   END trace_prog_name; 
 
   PROCEDURE gen_trace_proc (arg_type_in   IN VARCHAR2, 
                             args_in          arg_info_aat, 
                             comment_in    IN VARCHAR2) 
   IS 
   BEGIN 
      IF args_in.COUNT > 0 
      THEN 
         DBMS_OUTPUT.put_line ('l_params.DELETE;'); 
         DBMS_OUTPUT.put_line ('   ' || comment_in); 
         DBMS_OUTPUT.put_line (' BEGIN '); 
 
         FOR indx IN 1 .. args_in.COUNT 
         LOOP 
            DBMS_OUTPUT.put_line ( 
                  'logger.append_param(l_params, ''' 
               || args_in (indx).argument_name 
               || ''', ' 
               || args_in (indx).argument_name 
               || ');'); 
         END LOOP; 
 
         DBMS_OUTPUT.put_line ( 
            'logger.log(''START'', l_scope, NULL, l_params);'); 
 
         DBMS_OUTPUT.put_line ('END ' 
            || trace_prog_name (arg_type_in) 
            || ';'); 
      END IF; 
   END gen_trace_proc; 
BEGIN 
   get_argument_info (l_in_args, l_out_args); 
 
   /* Place procedures in anonymous block for easy formatting. */ 
   DBMS_OUTPUT.put_line ('DECLARE'); 
   DBMS_OUTPUT.put_line ( 
      'l_scope logger_logs.scope%type := ' || c_prog_name || ';'); 
   DBMS_OUTPUT.put_line ('l_params logger.tab_param;'); 
          
   gen_trace_proc ('IN', 
                   l_in_args, 
                   '/* AFTER ENTERING - IN and IN OUT argument tracing */'); 
   gen_trace_proc ('OUT', 
                   l_out_args, 
                   '/* BEFORE LEAVING - OUT and IN OUT argument tracing */'); 
 
   /* Place procedures in anonymous block for easy formatting. */ 
   DBMS_OUTPUT.put_line ('BEGIN'); 
 
   IF l_in_args.COUNT > 0 
   THEN 
      DBMS_OUTPUT.put_line (trace_prog_name ('IN') || '();'); 
   END IF; 
 
   DBMS_OUTPUT.put_line ('/*'); 
   DBMS_OUTPUT.put_line ('   Your Code Here!'); 
   DBMS_OUTPUT.put_line ('*/'); 
 
   IF l_out_args.COUNT > 0 
   THEN 
      DBMS_OUTPUT.put_line (trace_prog_name ('OUT') || '();'); 
   END IF; 
 
   DBMS_OUTPUT.put_line ('logger.log(''END'', l_scope);'); 
 
   DBMS_OUTPUT.put_line ('EXCEPTION WHEN OTHERS THEN'); 
 
   DBMS_OUTPUT.put_line ( 
      'logger.log_error(''Unhandled Exception'', l_scope, null, l_params);'); 
   DBMS_OUTPUT.put_line ('raise;'); 
 
   DBMS_OUTPUT.put_line ('END;'); 
END;

Set Up Program Units for Demonstration

CREATE OR REPLACE PROCEDURE show_emp_stats ( 
   employee_id_in      IN     INTEGER, 
   summary_out            OUT VARCHAR2, 
   total_comp_in       IN     NUMBER, 
   last_complaint_in   IN     VARCHAR2) 
IS 
BEGIN 
   /* Help! I need tracing or my parameters right here! */ 
   NULL; 
END; 

Set Up Program Units for Demonstration

CREATE OR REPLACE PACKAGE my_pkg 
IS 
   PROCEDURE show_emp_stats (employee_id_in      IN     INTEGER, 
                             summary_out            OUT VARCHAR2, 
                             total_comp_in       IN     NUMBER, 
                             last_complaint_in   IN     VARCHAR2); 
END; 

Set Up Program Units for Demonstration

CREATE OR REPLACE PACKAGE BODY my_pkg 
IS 
   PROCEDURE show_emp_stats (employee_id_in      IN     INTEGER, 
                             summary_out            OUT VARCHAR2, 
                             total_comp_in       IN     NUMBER, 
                             last_complaint_in   IN     VARCHAR2) 
   IS 
   BEGIN 
      /* Help! I need tracing or my parameters right here! */ 
      NULL; 
   END; 
END; 

Demonstrate of Utility

BEGIN 
   gen_logger_param_trace (pkg_or_prog_in => 'SHOW_EMP_STATS'); 
END; 

Demonstrate of Utility

BEGIN 
   gen_logger_param_trace ( 
   	  pkg_or_prog_in => 'MY_PKG', pkg_subprog_in => 'SHOW_EMP_STATS'); 
END;