[Debug PL/SQL Procedure] Function to collect variables into a debug table #sql

debug_pkg.sql

DROP table OM_DEBUG;

CREATE TABLE OM_DEBUG (
file_name VARCHAR2(50),
line_number number,
var_name  VARCHAR2(500),
var_text_value  VARCHAR2(2000),
var_num_value number,
precedence number,
timestamp  		timestamp(6),
orderId number(18) null
);


create or replace package om_debug_pkg
as

PROCEDURE debug(
  a_order_id        integer  default null,
  a_file_name       varchar2,
  a_line_number     integer,
  a_var_name        varchar2,
  a_var_text_value  varchar2 default null,
  a_var_num_value   integer  default null
    );
  
end;
/

create or replace package body om_debug_pkg
as

v_precedence number :=0;

PROCEDURE debug(
  a_order_id        integer  default null,
  a_file_name       varchar2,
  a_line_number     integer,
  a_var_name        varchar2,
  a_var_text_value  varchar2 default null,
  a_var_num_value   integer  default null
  ) IS

PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN
  
  v_precedence:=v_precedence+1;
  
  INSERT INTO OM_DEBUG
  (file_name, line_number, var_name, var_text_value, var_num_value, precedence, timestamp, orderId)
  VALUES
  (a_file_name, a_line_number, a_var_name, a_var_text_value, a_var_num_value, v_precedence, systimestamp, a_order_id);

  COMMIT; 
END debug;

end;
/

debugPkgUsage.sql

------------------------------------------
set echo off 
set serveroutput on
set timing off
set pages 25

column VAR_NAME format a50;
column VAR_TEXT_VALUE format a75;
column VAR_NUM_VALUE format 9999999999;
column TIMESTAMP format a33;


select VAR_NAME,VAR_TEXT_VALUE,VAR_NUM_VALUE  from om_debug  where orderId=&&1 order by precedence;

------------------------------------------------------

v_values varchar2(500);
v_values := v_row.order_seq_id || ':' || v_row.order_hist_seq_id || ':' || v_row.notification_id || ':' ||
            v_row.notification_hist_seq_id || ':' || v_row.data_modification_id;
om_debug_pkg.debug(a_order_seq_id, 'om_orch_comp_errors_pkg', 94, 'values', v_values, null) ;