CODEWITHSUNDEEP
oracle-databaseplsql
Eli Abramson
Eli Abramson

Reputation: 129

Does Oracle have a record for which procedure performed DML on a specified table?

Does Oracle have a view / table (sys or not), storing the name of the stored procedure/function that performed a DML on a specified table? For example, I have 3 procedures working with the same table. An application is calling each procedure according to an internal logic. I would like to know which of the procedures was called in order to perform a DML. Obviously, the application could have logged this, but in my case, it hasn't.

Upvotes: 0

Views: 63

Answers (1)

Carlo Sirna
Carlo Sirna

Reputation: 1251

in DBMS_UTILITY there is the FORMAT_CALL_STACK function, which, when called inside any pl/sql procedure or trigger, returns the stack of all procedures that have been called to reach your code (it lists the caller, the caller of the caller, and so on).

You could write a trigger like this one:

    create or replace trigger TRG_LOG_MYTABLE_EDITS
      after insert or delete or update
      on MYTABLE 
      for each row -- see following note about this option
    declare 
      op varchar2(1);
    begin
      if INSERTING then op := 'I';
      elsif DELETING then op := 'D';
      else op := 'U';
      end if;

      insert into mylog(DATETIME, OPERATION, CALL_STACK) 
      values ( sysdate, op, DBMS_UTILITY.format_call_stack);

    end TRG_LOG_MYTABLE_EDITS;

This trigger will add an entry to the table MYLOG every time a record is inserted/deleted or updated... and in this table you will find the references to the procedures that modified the table.

Note: the above trigger will be executed FOR EACH ROW.. so if you issue a single update command that modifies 1000 rows, you will get the same command being logged 1000 times. If you just want to log the fact that someone has issued a "delete" command (not caring about how many rows have been deleted) you can omit the "for each row" line in the declaration of the trigger.

Leaving the "for each row" option allows you to access the :OLD and :NEW "pseudorecords" containing the new and old values of the row being modified. You could log them too, or you could test their values to write the log only if some particular changes have been performed

Upvotes: 1

Related Questions