Combining Oracle trigger and DBMS_UTILITY.FORMAT_CALL_STACK to track transactions

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