blocks|key|577344|text|我找到了这个论坛：http://www.orafaq.com/forum/t/60583/0/。这可能就是你正在寻找的东西。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|577345|基本上，您可以使用Oracle提供的dbms_utility.format_call_stack|style|CODE|577346|scott@ORA92>+CREATE+TABLE+error_tab
++2++++(who_am_i++++++VARCHAR2(61),
++3+++++who_called_me+VARCHAR2(61),
++4+++++call_stack++++CLOB)
++5++/

Table+created.

scott@ORA92>+
scott@ORA92>+CREATE+OR+REPLACE+PROCEDURE+d
++2++AS
++3++++v_num++++++NUMBER;
++4++++v_owner++++VARCHAR2(30);
++5++++v_name+++++VARCHAR2(30);
++6++++v_line+++++NUMBER;
++7++++v_caller_t+VARCHAR2(100);
++8++BEGIN
++9++++select+to_number('a')+into+v_num+from+dual;+--+cause+error+for+testing
+10++EXCEPTION
+11++++WHEN+OTHERS+THEN
+12++++++who_called_me+(v_owner,+v_name,+v_line,+v_caller_t);
+13++++++INSERT+INTO+error_tab
+14++++++VALUES+(who_am_i,
+15++++++++++v_owner+%7C%7C+'.'+%7C%7C+v_name,
+16++++++++++dbms_utility.format_call_stack);
+17++END+d;
+18++/

Procedure+created.

scott@ORA92>+SHOW+ERRORS
No+errors.
scott@ORA92>+CREATE+OR+REPLACE+PROCEDURE+c
++2++AS
++3++BEGIN
++4++++d;
++5++END+c;
++6++/

Procedure+created.

scott@ORA92>+CREATE+OR+REPLACE+PROCEDURE+b
++2++AS
++3++BEGIN
++4++++c;
++5++END+b;
++6++/

Procedure+created.

scott@ORA92>+CREATE+OR+REPLACE+PROCEDURE+a
++2++AS
++3++BEGIN
++4++++b;
++5++END+a;
++6++/

Procedure+created.

scott@ORA92>+execute+a

PL/SQL+procedure+successfully+completed.

scott@ORA92>+COLUMN+who_am_i+FORMAT+A13
scott@ORA92>+COLUMN+who_called_me+FORMAT+A13
scott@ORA92>+COLUMN+call_stack++++FORMAT+A45
scott@ORA92>+SELECT+*+FROM+error_tab
++2++/

WHO_AM_I++++++WHO_CALLED_ME+CALL_STACK
-------------+-------------+---------------------------------------------
SCOTT.D+++++++SCOTT.C+++++++-----+PL/SQL+Call+Stack+-----
++++++++++++++++++++++++++++++object++++++line++object
++++++++++++++++++++++++++++++handle++++number++name
++++++++++++++++++++++++++++6623F488+++++++++1++anonymous+block
++++++++++++++++++++++++++++66292138++++++++13++procedure+SCOTT.D
++++++++++++++++++++++++++++66299430+++++++++4++procedure+SCOTT.C
++++++++++++++++++++++++++++6623D2F8+++++++++4++procedure+SCOTT.B
++++++++++++++++++++++++++++6624F994+++++++++4++procedure+SCOTT.A
++++++++++++++++++++++++++++66299984+++++++++1++anonymous+block


scott@ORA92>|code-block|syntax|javascript|577347|entityMap|0|LINK|mutability|MUTABLE|url|http://www.orafaq.com/forum/t/60583/0/^0|9|12|0|0|I|U|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@$A|V|B|W|1|X]]|C|$]]|$1|D|3|E|5|6|7|Y|8|@$A|Z|B|10|F|G]]|9|@]|C|$]]|$1|H|3|I|5|J|7|11|8|@]|9|@]|C|$K|L]]|$1|M|3|-4|5|6|7|12|8|@]|9|@]|C|$]]]|N|$O|$5|P|Q|R|C|$S|T]]]]

I found this forum: <a href="http://www.orafaq.com/forum/t/60583/0/">http://www.orafaq.com/forum/t/60583/0/</a>. It may be what you are looking.

Basically, you can use the Oracle supplied <code>dbms_utility.format_call_stack</code>:

<pre><code>scott@ORA92&gt; CREATE TABLE error_tab
 2 (who_am_i VARCHAR2(61),
 3 who_called_me VARCHAR2(61),
 4 call_stack CLOB)
 5 /

Table created.

scott@ORA92&gt; 
scott@ORA92&gt; CREATE OR REPLACE PROCEDURE d
 2 AS
 3 v_num NUMBER;
 4 v_owner VARCHAR2(30);
 5 v_name VARCHAR2(30);
 6 v_line NUMBER;
 7 v_caller_t VARCHAR2(100);
 8 BEGIN
 9 select to_number('a') into v_num from dual; -- cause error for testing
 10 EXCEPTION
 11 WHEN OTHERS THEN
 12 who_called_me (v_owner, v_name, v_line, v_caller_t);
 13 INSERT INTO error_tab
 14 VALUES (who_am_i,
 15 v_owner || '.' || v_name,
 16 dbms_utility.format_call_stack);
 17 END d;
 18 /

Procedure created.

scott@ORA92&gt; SHOW ERRORS
No errors.
scott@ORA92&gt; CREATE OR REPLACE PROCEDURE c
 2 AS
 3 BEGIN
 4 d;
 5 END c;
 6 /

Procedure created.

scott@ORA92&gt; CREATE OR REPLACE PROCEDURE b
 2 AS
 3 BEGIN
 4 c;
 5 END b;
 6 /

Procedure created.

scott@ORA92&gt; CREATE OR REPLACE PROCEDURE a
 2 AS
 3 BEGIN
 4 b;
 5 END a;
 6 /

Procedure created.

scott@ORA92&gt; execute a

PL/SQL procedure successfully completed.

scott@ORA92&gt; COLUMN who_am_i FORMAT A13
scott@ORA92&gt; COLUMN who_called_me FORMAT A13
scott@ORA92&gt; COLUMN call_stack FORMAT A45
scott@ORA92&gt; SELECT * FROM error_tab
 2 /

WHO_AM_I WHO_CALLED_ME CALL_STACK
------------- ------------- ---------------------------------------------
SCOTT.D SCOTT.C ----- PL/SQL Call Stack -----
 object line object
 handle number name
 6623F488 1 anonymous block
 66292138 13 procedure SCOTT.D
 66299430 4 procedure SCOTT.C
 6623D2F8 4 procedure SCOTT.B
 6624F994 4 procedure SCOTT.A
 66299984 1 anonymous block


scott@ORA92&gt;
</code></pre>

blocks|key|577411|text|基本上，您所需要做的就是定义var，并在调用中将它们传递给一个实用方法，以便用值填充它们：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|577412|create+or+replace+procedure+some_test_proc+(p_some_int+int)+
is
++++owner_name+VARCHAR2+(100);
++++caller_name+VARCHAR2+(100);
++++line_number+NUMBER;
++++caller_type+VARCHAR2+(100);
begin
++++....
++++OWA_UTIL.WHO_CALLED_ME+(owner_name,caller_name,line_number,caller_type);
++++--+now+you+can+insert+those+values+along+with+systimestamp+into+a+log+file
++++....
end;|code-block|syntax|javascript|577413|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Basically, all you need to do is to define vars and pass them in a call to a utility method to fill them up with values:
<pre><code>create or replace procedure some_test_proc (p_some_int int) 
is
 owner_name VARCHAR2 (100);
 caller_name VARCHAR2 (100);
 line_number NUMBER;
 caller_type VARCHAR2 (100);
begin
 ....
 OWA_UTIL.WHO_CALLED_ME (owner_name,caller_name,line_number,caller_type);
 -- now you can insert those values along with systimestamp into a log file
 ....
end;
</code></pre>

Does anyone know whether it's possible for a PL/SQL procedure (an error-logging one in this case) to get the name of the function/procedure which called it?

Obviously I could pass the name in as a parameter, but it'd be nice to make a system call or something to get the info - it could just return null or something if it wasn't called from a procedure/function.

If there's no method for this that's fine - just curious if it's possible (searches yield nothing).

Get the name of the calling procedure or function in Oracle PL/SQL

有没有人知道PL/SQL过程(在本例中是一个错误日志记录过程)是否可以获得调用它的函数/过程的名称？显然，我可以将name作为参数传递，但最好是进行系统调用或其他方式来获取信息-如果不是从过程/函数调用，它可能只返回null或其他值。如果没有解决这个问题的方法，那也没关系--只是好奇它是否可行(搜索什么都没有)。

问在Oracle PL/SQL中获取调用过程或函数的名称
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在Oracle PL/SQL中获取调用过程或函数的名称EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在Oracle PL/SQL中获取调用过程或函数的名称
EN