Capture package / procedure / function name from trigger

Question

I have a table (Oracle 11g) on ​​which several packages / stored processes run DML instructions. I want to capture the name of the package / procedure that issued the DML in the table using the trigger and registering it in the logging table.

For instance:

The package MY_PACK.MY_PROC() throws insert into... for the table mytab . I would develop a trigger on mytab that should be able to capture the name of the package / procedure that issued insert into.. and save this information in another my_tab_log table.

I did some searching and found that $$PLSQL_UNIT and $$PLSQL_LINE can indicate the name of the procedure, but then if these variables are used from the trigger, the name of the trigger will be written instead of the name of the package / procedure that issued the DML instruction.

like -

 CREATE OR REPLACE TRIGGER my_trg AFTER INSERT OR UPDATE OR DELETE ON MY_TAB FOR EACH ROW BEGIN IF INSERTING THEN insert into my_tab_log values('INSERTED A ROW' sysdate, $$PLSQL_UNIT); END IF; -- This would capture Trigger name but I would like to capture `MY_PACK.MY_PROC()` -- which issued the insert statement ... END; 

Now, since $$ PLSQL_UNIT is a conditional compilation directive. It is allowed when compiling / recompiling PL / SQL code. Unfortunately, $$ PLSQL_UNIT inside a trigger is nothing more than the name of the trigger and is resolved when compilation starts.

I also found the owa_util.who_called_me procedure, but could not wrap my head around how I could use it to meet my needs. Is it possible to achieve what I want without modifying the actual packages / stored procedures that trigger the DML statements? I can’t change these programs, and this is a strict restriction on him, so this is not an option.

Answer 1

$$PLSQL_UNIT will only indicate the package name, not the procedure name inside the package. The same goes for who_called_me .

owa_util.who_called_me based on a little utility written by the invaluable Mr. Keith. If you look at its source code here , you will see that the routine gets its information from the call stack. Therefore, the information he offers is:

• program owner
• program name (package or separate procedure)
• type of program
• line number

These admittedly disappointing restrictions come down to overload: we can create packaged procedures with the same name, but different signatures. Therefore, the "procedure name" is not particularly useful for the system when it comes to determining which part of the code is working.

In any case, if you want to play with who_called_me , the following four parameters are required for this:

 create or replace trigger my_trg before insert or update on my_tab for each row declare l_owner varchar2(30); l_name varchar2(30); l_line pls_integer; l_type varchar2(30); begin owa_util.who_called_me(l_owner,l_name,l_line,l_type); IF INSERTING THEN insert into my_tab_log values('INSERTED A ROW' sysdate, l_owner||'.'||l_name); END IF; end; /