Oracle 12 PL/SQL retrieving a stored procedure name within a trigger

Question

I have a situation by using a trigger I want to record the name of the stored procedure that initiated a stack of stored procedure calls that ultimately perform an INSERT into the table that the trigger is monitoring. This trigger exists solely for debug purposes where there are multiple stored procedures that will INSERT records in the table that the trigger is monitoring and recording the invoking stored procedure's name will help to identify the context in which error conditions are occurring.

Constraint: We do not want (are not permitted) to alter the wrapped stored procedures that the trigger is monitoring because these are provided by a third-party vendor who will not support the stored procedures if these are unwrapped or altered.

Is there an accessible call stack in PL/SQL that can be inspected within a trigger? and if so, how can I access the call stack to obtain the required information?

---

Edit:

Prompted by William Robertson's comment I found UTL_CALL_STACK that apparently will give me the name of the initial procedure that was called as follows:

UTL_CALL_STACK.concatenate_subprogram (
    UTL_CALL_STACK.subprogram (
        UTL_CALL_STACK.dynamic_depth
    )
)

Here's the link that I found that describes UTL_CALL_STACK:

https://oracle-base.com/articles/12c/utl-call-stack-12cr1

Answer 1

As others have stated in your comments, the only option is to parse the call stack. I've created an example below on how you can do that is through a trigger.

Demo Tables

CREATE TABLE test_ids
(
    id    NUMBER
);

CREATE TABLE insert_log
(
    insert_date          DATE,
    insert_value         NUMBER,
    call_stack           VARCHAR2 (4000),
    calling_procedure    VARCHAR2 (4000)
);

Demo Trigger

The trigger below will record each insert into the test_ids table in the insert_log table. You can store either the full stack of DBMS_UTILITY.format_call_stack to assist you with your troubleshooting or just the calling procedure. In the demo I have built, the calling procedure of the insert will always be stored on the 5th line (4th line is the trigger) starting at the 23rd position of the line.

CREATE OR REPLACE TRIGGER test_ids_after_insert
    AFTER INSERT
    ON test_ids
    FOR EACH ROW
DECLARE
    l_stack   VARCHAR2 (4000) := DBMS_UTILITY.format_call_stack;
BEGIN
    INSERT INTO insert_log (insert_date,
                            insert_value,
                            call_stack,
                            calling_procedure)
         VALUES (SYSDATE,
                 :NEW.id,
                 l_stack,
                 SUBSTR (l_stack,
                           INSTR (l_stack,
                                  CHR (10),
                                  1,
                                  4)
                         + 23,
                           INSTR (l_stack,
                                  CHR (10),
                                  1,
                                  5)
                         - (  INSTR (l_stack,
                                     CHR (10),
                                     1,
                                     4)
                            + 23)));
END;
/

Test Procedure

Just to demo some different output of the trigger

CREATE OR REPLACE PROCEDURE test_insert (p_num NUMBER)
IS
BEGIN
    INSERT INTO test_ids
         VALUES (p_num);
END;
/

Test Inserts

INSERT INTO test_ids
     VALUES (1);

DECLARE
    PROCEDURE insert_function (p_num NUMBER)
    IS
    BEGIN
        INSERT INTO test_ids
             VALUES (p_num);
    END;
BEGIN
    INSERT INTO test_ids
         VALUES (2);

    insert_function (3);
    test_insert (4);
END;
/

Demo Results

This is what the results of insert_log would look like. There would be no calling procedure if an insert statement was executed by itself, anonymous blocks get recorded as anonymous blocks, and if it's called from a procedure/function/package, that object's name would get stored.

   INSERT_DATE    INSERT_VALUE                                          CALL_STACK                CALLING_PROCEDURE
______________ _______________ ___________________________________________________ ________________________________
09-OCT-20                    1 ----- PL/SQL Call Stack -----
                               object      line  object
                               handle    number  name
                               0x6ab98970         2  EJSTEST.TEST_IDS_AFTER_INSERT

09-OCT-20                    2 ----- PL/SQL Call Stack -----                       anonymous block
                               object      line  object
                               handle    number  name
                               0x6ab98970         2  EJSTEST.TEST_IDS_AFTER_INSERT
                               0x6727e250         9  anonymous block

09-OCT-20                    3 ----- PL/SQL Call Stack -----                       anonymous block
                               object      line  object
                               handle    number  name
                               0x6ab98970         2  EJSTEST.TEST_IDS_AFTER_INSERT
                               0x6727e250         5  anonymous block
                               0x6727e250        12  anonymous block

09-OCT-20                    4 ----- PL/SQL Call Stack -----                       procedure EJSTEST.TEST_INSERT
                               object      line  object
                               handle    number  name
                               0x6ab98970         2  EJSTEST.TEST_IDS_AFTER_INSERT
                               0x615b3ce0         4  procedure EJSTEST.TEST_INSERT
                               0x6727e250        13  anonymous block