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

问题描述

我有一个表 (Oracle 11g)，多个包/存储过程在该表上运行 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屋！