I recently encountered a situation where a small number of records in a large Oracle table contain wrong values, and naturally I need to find out exactly which program is causing this problem. I decided to use Oracle triggers to do this job, making use of the built-in DBMS_UTILITY.FORMAT_CALL_STACK function as the main ingredient.
create or replace trigger trg_stack_trace_logger before insert or update on inventory_table for each row begin if (:old.expiration_date <> :new.expiration_date) then insert into stack_trace_log values( 'User=' || user || '; ' || 'Date=' || to_char(sysdate,'mm/dd/yyyy hh24:mi:ss') || '; ' || 'Old Value=' || :old.expiration_date || '; ' || 'New Value=' || :new.expiration_date || '; ' || DBMS_UTILITY.FORMAT_CALL_STACK ); end if; end;
As you can see, the output contains both old/new values of the transaction as well as some metadata (ie. the stack trace) of the transaction itself. The output is inserted into a table called “stack_trace_log”, which, for simplicity sake, is just a table consisted of a single varchar2 field; if you will use this type of tracking over a longer period, it is probably best to track username, date, etc. in their own fields for better reporting capabilities.
select * from stack_trace_log; LOGGED_INFO --------------- User=JOE; Date=11/24/2015 08:33:39; Old Value=2015-11-15-00.00.00; New Value=2030-11-15-08.33.39; ----- PL/SQL Call Stack ----- object handle line number object name 0x91626018 1 anonymous block 0x8dcb7b30 3 ERP.TRG_STACK_TRACE_LOGGER 0x9657ec50 354 package body ERP.INVENTORY_API 0x9657ec50 1483 package body ERP.INVENTORY_API 0x8c7d2758 4254 package body ERP.INVENTORY_API 0x969315a0 650 package body ERP.RECEIVING_API 0x969315a0 3524 package body ERP.RECEIVING_API 0x969315a0 2861 package body ERP.RECEIVING_API 0x91411208 342 package body ERP.BARCODE_ARRIVAL_API 0x8bd5cca8 1 anonymous block 0x82871f48 1120 package body SYS.DBMS_SYS_SQL 0x82886f48 323 package body SYS.DBMS_SQL 0x99f8e6c0 138 package body ERP.BARCODE_INTERFACE_API 0x93980f88 1 anonymous block 1 rows selected