从触发器捕获包/过程/函数名称 [英] Capturing Package/Procedure/Function name from a trigger

问题描述

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

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.

例如:

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

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

我进行了一些搜索，发现$$PLSQL_UNIT和$$PLSQL_LINE可以指出过程名称，但是如果在触发器中使用这些变量，则将捕获触发器名称，而不是包/过程名称.发布了DML声明.

I did some search and found that $$PLSQL_UNIT and $$PLSQL_LINE could 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.

喜欢-

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;

现在，因为$$ PLSQL_UNIT是条件编译指令.当您编译/重新编译PL/SQL代码时，将解决该问题.因此，不幸的是，触发器内的$$ PLSQL_UNIT只不过是触发器名称而已，并且在触发器编译时就已解决.

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.

我也找到了过程owa_util.who_called_me，但是无法确定如何使用它来满足自己的需求.在不更改发出DML语句的实际包/存储过程的情况下，是否有可能实现我想要的目标?我不能修改这些程序，这是对它的严格限制，所以这不是一个选择.

I also found the procedure owa_util.who_called_me but 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.

推荐答案

$$PLSQL_UNIT仅提供程序包名称，而不提供程序包中过程的名称. who_called_me也是一样.

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

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

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

• 程序所有者
• 程序名称(程序包或独立过程)
• 程序类型
• 行号

这些令人沮丧的局限性在于重载:我们可以创建具有相同名称但具有不同签名的打包过程.因此，过程名称"在识别哪个代码正在运行时对系统不是特别有用.

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.

无论如何，如果您想和who_called_me一起玩，它将需要四个类似这样的参数:

Anyway, if you want to have a play with who_called_me, it takes four out parameters like 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;
/

这篇关于从触发器捕获包/过程/函数名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！