Check Who is updating Column of a Table in Oracle

Many times while trying to Debug we can't find out the piece of code which is updating any particular columns of a table.

Here we have an Oracle utility which tells you who called this package.

Example : Trigger :- I have written a simple DB trigger which will log every event occurs on trx_number column of ra_customer_Trx_all.

CREATE OR REPLACE TRIGGER APPS.XX_AR_CUSTOM_TRIGGER

   AFTER UPDATE

   ON "AR"."RA_CUSTOMER_TRX_ALL#"

   REFERENCING NEW AS NEW OLD AS OLD

   FOR EACH ROW

DECLARE

   l_owner         VARCHAR2 (30);

   l_name          VARCHAR2 (30);

   l_lineno        NUMBER;

   l_caller_t      VARCHAR2 (255);

   l_updates_str   CLOB;

   --

   PROCEDURE write_log

   IS

   BEGIN

      --      log in a table

      NULL;

   END;

BEGIN

   OWA_UTIL.who_called_me (l_owner,

                           l_name,

                           l_lineno,

                           l_caller_t);

   l_updates_str :=

         l_owner

      || '.'

      || l_name

      || ' on line  '

      || l_lineno

      || '.Trx_id='

      || :NEW.customer_trx_id;

   IF UPDATING

   THEN

      l_updates_str := l_updates_str || ' UPDATE: ';

      IF     NVL (:NEW.trx_number, :OLD.trx_number) IS NOT NULL

         AND NVL (:NEW.trx_number, :OLD.trx_number + 1) <>

                NVL (:OLD.trx_number, :NEW.trx_number + 1)

      THEN

         l_updates_str :=

            l_updates_str || 'trx_number="' || :NEW.trx_number || '";';

      END IF;

   END IF;

   write_log;

EXCEPTION

   WHEN OTHERS

   THEN                                      -- caution handles all exceptions

      write_log ('Error :: ' || SQLERRM);

END;

/