{"id":151,"date":"2015-11-24T10:06:23","date_gmt":"2015-11-24T15:06:23","guid":{"rendered":"http:\/\/www.dev-notes.com\/blog\/2015\/11\/24\/combining-oracle-trigger-and-dbms_utility-format_call_stack-to-track-transactions\/"},"modified":"2015-11-24T10:06:23","modified_gmt":"2015-11-24T15:06:23","slug":"combining-oracle-trigger-and-dbms_utility-format_call_stack-to-track-transactions","status":"publish","type":"post","link":"https:\/\/www.dev-notes.com\/blog\/2015\/11\/24\/combining-oracle-trigger-and-dbms_utility-format_call_stack-to-track-transactions\/","title":{"rendered":"Combining Oracle trigger and DBMS_UTILITY.FORMAT_CALL_STACK to track transactions"},"content":{"rendered":"<p>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.<\/p>\n<pre class=\"code\">\ncreate or replace trigger trg_stack_trace_logger\nbefore insert or update on inventory_table\nfor each row\nbegin\n\tif (:old.expiration_date <> :new.expiration_date) then\n\t\tinsert into stack_trace_log\n\t\tvalues(\n\t\t'User=' || user || '; ' ||\n\t\t'Date=' || to_char(sysdate,'mm\/dd\/yyyy hh24:mi:ss') || '; ' ||\n\t\t'Old Value=' || :old.expiration_date || '; ' ||\n\t\t'New Value=' || :new.expiration_date || '; ' ||\n\t\tDBMS_UTILITY.FORMAT_CALL_STACK\n\t\t);\n\tend if;\nend;\n<\/pre>\n<p>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 &#8220;stack_trace_log&#8221;, 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.<\/p>\n<pre class=\"code\">\nselect * from stack_trace_log;\n\nLOGGED_INFO\n---------------\nUser=JOE; \nDate=11\/24\/2015 08:33:39; \nOld Value=2015-11-15-00.00.00; \nNew Value=2030-11-15-08.33.39; \n----- PL\/SQL Call Stack -----    \nobject handle line number object name\n0x91626018    1           anonymous block\n0x8dcb7b30    3           ERP.TRG_STACK_TRACE_LOGGER\n0x9657ec50    354         package body ERP.INVENTORY_API\n0x9657ec50    1483        package body ERP.INVENTORY_API\n0x8c7d2758    4254        package body ERP.INVENTORY_API\n0x969315a0    650         package body ERP.RECEIVING_API\n0x969315a0    3524        package body ERP.RECEIVING_API\n0x969315a0    2861        package body ERP.RECEIVING_API\n0x91411208    342         package body ERP.BARCODE_ARRIVAL_API\n0x8bd5cca8    1           anonymous block\n0x82871f48    1120        package body SYS.DBMS_SYS_SQL\n0x82886f48    323         package body SYS.DBMS_SQL\n0x99f8e6c0    138         package body ERP.BARCODE_INTERFACE_API\n0x93980f88    1           anonymous block\n\n1 rows selected\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>This example demonstrates how to combine Oracle trigger and the built-in DBMS_UTILITY.FORMAT_CALL_STACK function to create a tool to trace what program may be creating bad data.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[20],"tags":[],"class_list":["post-151","post","type-post","status-publish","format-standard","hentry","category-oracle"],"_links":{"self":[{"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/posts\/151","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/comments?post=151"}],"version-history":[{"count":0,"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/posts\/151\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/media?parent=151"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/categories?post=151"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/tags?post=151"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}