oracle 从触发器中捕获包/过程/函数名称

Capturing Package/Procedure/Function name from a trigger

提问by AnBisw

I have a table (Oracle 11g) on which multiple packages/stored procs run DML statements. I want to capture the package/procedure name which issued the DML on the table using a trigger and log it into a logging table.

我有一个表（Oracle 11g），多个包/存储过程在该表上运行 DML 语句。我想使用触发器捕获在表上发出 DML 的包/过程名称，并将其记录到日志记录表中。

For example:

例如：

Package MY_PACK.MY_PROC()issues an insert into...for the table mytab. I would design a trigger on mytabwhich should be able to capture the package/procedure name that issued the insert into..and store this info in another table my_tab_log.

包对表MY_PACK.MY_PROC()发出。我会设计一个触发器，它应该能够捕获发布的包/过程名称并将此信息存储在另一个表中。insert into...mytabmytabinsert into..my_tab_log

I did some search and found that $$PLSQL_UNITand $$PLSQL_LINEcould point out the procedure name, but then if these variables are used from within a trigger, the trigger name would be captured instead of the package/procedure name which issued the DML statement.

我做了一些搜索和发现，$$PLSQL_UNIT并$$PLSQL_LINE能指出该过程的名称，但随后如果这些变量从一个触发器内使用，触发器名字将被捕获，而不是颁发DML语句包/程序的名称。

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 resolved when you complie/recompile PL/SQL code. So unfortunately, $$PLSQL_UNIT within trigger is nothing more but trigger name and is resolved at trigger compile time.

现在因为 $$PLSQL_UNIT 是一个条件编译指令。当您编译/重新编译 PL/SQL 代码时，它会得到解决。所以不幸的是，触发器中的 $$PLSQL_UNIT 只不过是触发器名称，并在触发器编译时解析。

I also found the procedure owa_util.who_called_mebut couldn't wrap my head around on how I could use this to fit my needs. Is that even possible to achieve what I want without making changes to the actual packages/stored procs which issues the DML statements? I cannot modify those programs and that is a hard restriction to it so that is not an option.

我也找到了程序，owa_util.who_called_me但无法理解如何使用它来满足我的需求。甚至可以在不更改发出 DML 语句的实际包/存储过程的情况下实现我想要的吗？我无法修改这些程序，这是对它的严格限制，因此不是一种选择。

回答by APC

$$PLSQL_UNITwould only provide the package name, not the name of the procedure within the package. The same is true of who_called_me.

$$PLSQL_UNIT将只提供包名称，而不是包中的过程名称。也是如此who_called_me。

owa_util.who_called_meis based on a little utility written by the inestimable Mr Kyte. If you take a peek at his source code hereyou will see that the routine gets its information from the call stack. Hence the information it offers is:

owa_util.who_called_me是基于不可估量的 Kyte 先生编写的一个小实用程序。如果您在这里查看他的源代码，您将看到该例程从调用堆栈中获取其信息。因此，它提供的信息是：

• program owner
• program name (package, or standalone procedure)
• program type
• line number

• 程序所有者
• 程序名称（包或独立过程）
• 节目类型
• 电话号码

These admittedly frustrating limitations are down to overloading: we can create packaged procedures with the same name but different signatures. Consequently "procedure name" is not particularly useful to the system when it comes to identifying which piece of code is operating.

这些公认的令人沮丧的限制归结为重载：我们可以创建具有相同名称但不同签名的打包过程。因此，在识别正在运行的代码段时，“过程名称”对系统并不是特别有用。

Anyway, if you want to have a play with who_called_me, it takes four out parameters like this:

无论如何，如果你想玩玩who_called_me，它需要四个这样的参数：

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;
/