oracle 包中当前正在执行的过程名称

pxyaymoc  于 2023-04-29  发布在  Oracle
关注(0)|答案(4)|浏览(168)

有没有办法获取包中当前正在执行的过程的名称？

  1. create or replace package test_pkg
  2. as
  3.     procedure proc1;
  4. end test_pkg;
  6. create or replace package body test_pkg
  7. as
  8.     procedure proc1 
  9.     is
  10.         -- // Get the Procedure Name here?? //
  11.     end proc1;
  12. end test_pkg;
oracle

4条答案

按热度按时间
ssgvzors

ssgvzors1#

在12c中，当前的子程序名称只是：

  1. utl_call_stack.subprogram(1)(2);

目前的套餐也可以从

  1. utl_call_stack.subprogram(1)(1);

但通常更容易仅使用$$plsql_unit。您也可以获得限定名（package.procedure）：

  1. utl_call_stack.concatenate_subprogram(utl_call_stack.subprogram(1));

然而，我想不出任何情况下，一个过程或函数（或对象方法）会想要自己的名字。此功能在日志记录过程中最有用，在这种情况下 '谁叫我？' 代码应该在日志记录器中，而不是在调用它的每个事物中重复。因此，我强烈建议避免任何 '我是谁？' 程序中的逻辑。相反，将类似这样的东西放在日志记录器中（需要12个。1或更高版本）：

  1. create or replace procedure logdemo
  2. as
  3.     -- This is a package name, standalone function name, standalone procedure name,
  4.     --   or "__anonymous_block".
  5.     k_calling_subprogram1 varchar2(128);
  6.     -- This is a package procedure name or package function name, and it is NULL if
  7.     --   the parent is a standalone function, standalone procedure, or anonymous block.
  8.     k_calling_subprogram2 varchar2(128);
  9. begin
  10.     k_calling_subprogram1 := utl_call_stack.subprogram(2)(1);
  11.     begin
  12.         k_calling_subprogram2 := utl_call_stack.subprogram(2)(2);
  13.     exception when subscript_beyond_count then
  14.         k_calling_subprogram2 := null;
  15.     end;
  17.     dbms_output.put_line
  18.     ( $$plsql_unit ||
  19.       ' called from subprogram1 '||k_calling_subprogram1
  20.       ||', subprogram2 '||k_calling_subprogram2 );
  21. end logdemo;
  22. /

不幸的是，在11g中它有点复杂，因为你必须解析dbms_utility.format_call_stack，并且因为这只给你包名和行号（在换行符分隔的文本字符串中），然后你必须查询all_source来找到子程序名。
我可以张贴一些11g代码，如果你澄清它的用途。在我的11g logger中，我发现根据sqlcode等捕获dbms_utility.format_error_backtrace和dbms_utility.format_call_stack很有用，所以有一堆特定于日志记录的逻辑，如果你只是想捕获当前过程名，你可能不需要这些逻辑。

展开查看全部
赞(0）分享  回复(0）举报  2023-04-29
bq9c1y66

bq9c1y662#

如果它是一个独立的程序（i.即不是包内的那个），你会很容易得到答案：

  1. SQL> create or replace procedure p_test is
  2.   2  begin
  3.   3    dbms_output.put_line('I am ' || $$plsql_unit);
  4.   4    null;
  5.   5  end;
  6.   6  /
  8. Procedure created.
  10. SQL> exec p_test
  11. I am P_TEST
  13. PL/SQL procedure successfully completed.
  15. SQL>

对于包来说，事情不再那么简单了。上面的选项（使用$$plsql_unit）返回的是包体名称，而不是过程名称：

  1. SQL> create or replace package pkg_test as
  2.   2    procedure p_test_in_pkg;
  3.   3  end;
  4.   4  /
  6. Package created.
  8. SQL> create or replace package body pkg_test as
  9.   2    procedure p_test_in_pkg is
  10.   3    begin
  11.   4      dbms_output.put_line('Packaged procedure whose name is ' || $$plsql_unit);
  12.   5      null;
  13.   6    end;
  14.   7  end;
  15.   8  /
  17. Package body created.
  19. SQL> exec pkg_test.p_test_in_pkg;
  20. Packaged procedure whose name is PKG_TEST     --> should have been "p_test_in_pkg"
  22. PL/SQL procedure successfully completed.

以下功能似乎正在工作。作者是 garbuya，来源是OTN论坛的讨论Efficient WHO_AM_I and WHO_CALLED_ME讨论（2010年）。

  1. create or replace
  2. FUNCTION FN_WHO_AM_I ( p_lvl  NUMBER DEFAULT 0) RETURN VARCHAR2
  3. IS
  4. /***********************************************************************************************
  5. FN_WHO_AM_I returns the full ORACLE name of your object including schema and package names
  6. --
  7. FN_WHO_AM_I(0) - returns the name of your object
  8. FN_WHO_AM_I(1) - returns the name of calling object
  9. FN_WHO_AM_I(2) - returns the name of object, who called calling object
  10. etc., etc., etc.... Up to to he highest level
  11. -------------------------------------------------------------------------------------------------
  12. Copyrigth GARBUYA 2010
  13. *************************************************************************************************/
  14. TYPE str_varr_t   IS VARRAY(2) OF CHAR(1);
  15. TYPE str_table_t  IS TABLE OF VARCHAR2(256);
  16. TYPE num_table_t  IS TABLE OF NUMBER;
  17. v_stack           VARCHAR2(2048) DEFAULT UPPER(dbms_utility.format_call_stack);
  18. v_tmp_1           VARCHAR2(1024);
  19. v_tmp_2           VARCHAR2(1024);
  20. v_pkg_name        VARCHAR2(32);
  21. v_obj_type        VARCHAR2(32);
  22. v_owner           VARCHAR2(32);
  23. v_idx             NUMBER := 0;
  24. v_pos1            NUMBER := 0;
  25. v_pos2            NUMBER := 0;
  26. v_line_nbr        NUMBER := 0;
  27. v_blk_cnt         NUMBER := 0;
  28. v_str_len         NUMBER := 0;
  29. v_bgn_cnt         NUMBER := 0;
  30. v_end_cnt         NUMBER := 0;
  31. it_is_comment     BOOLEAN := FALSE;
  32. it_is_literal     BOOLEAN := FALSE;
  33. v_literal_arr     str_varr_t := str_varr_t ('''', '"');
  34. v_blk_bgn_tbl     str_table_t := str_table_t (' IF '   , ' LOOP '   , ' CASE ', ' BEGIN ');
  35. v_tbl             str_table_t := str_table_t();
  36. v_blk_bgn_len_tbl num_table_t := num_table_t();
  38. BEGIN
  40.    v_stack := SUBSTR(v_stack,INSTR(v_stack,CHR(10),INSTR(v_stack,'FN_WHO_AM_I'))+1)||'ORACLE'; -- skip myself
  42.    FOR v_pos2 in 1 .. p_lvl LOOP  -- advance to the input level
  43.       v_pos1 := INSTR(v_stack, CHR(10));
  44.       v_stack := SUBSTR(v_stack, INSTR(v_stack, CHR(10)) + 1);
  45.    END LOOP;
  47.    v_pos1 := INSTR(v_stack, CHR(10));
  48.    IF v_pos1 = 0 THEN
  49.       RETURN (v_stack);
  50.    END IF;
  52.    v_stack := SUBSTR(v_stack, 1, v_pos1 - 1);  -- get only current level
  53.    v_stack := TRIM(SUBSTR(v_stack, instr(v_stack, ' ')));  -- cut object handle
  54.    v_line_nbr := TO_NUMBER(SUBSTR(v_stack, 1, instr(v_stack, ' ') - 1));  -- get line number
  55.    v_stack := TRIM(SUBSTR(v_stack, instr(v_stack, ' ')));  -- cut line number
  56.    v_pos1 := INSTR(v_stack, ' BODY');
  57.    IF v_pos1  = 0 THEN
  58.       RETURN (v_stack);
  59.    END IF;
  61.    v_pos1 := INSTR(v_stack, ' ', v_pos1 + 2);  -- find end of object type
  62.    v_obj_type := SUBSTR(v_stack, 1, v_pos1 - 1);  -- get object type
  63.    v_stack := TRIM(SUBSTR(v_stack, v_pos1 + 1));  -- get package name
  64.    v_pos1 := INSTR(v_stack, '.');
  65.    v_owner := SUBSTR(v_stack, 1, v_pos1 - 1);  -- get owner
  66.    v_pkg_name  := SUBSTR(v_stack, v_pos1 + 1);  -- get package name
  67.    v_blk_cnt := 0;
  68.    it_is_literal := FALSE;
  69.    --
  70.    FOR v_idx in v_blk_bgn_tbl.FIRST .. v_blk_bgn_tbl.LAST
  71.    LOOP
  72.       v_blk_bgn_len_tbl.EXTEND(1);
  73.       v_blk_bgn_len_tbl (v_blk_bgn_len_tbl.last) := LENGTH(v_blk_bgn_tbl(v_idx));
  74.    END LOOP;
  75.    --
  76.    FOR src
  77.    IN ( SELECT ' '||REPLACE(TRANSLATE(UPPER(text), ';('||CHR(10), '   '),'''''',' ') ||' ' text
  78.         FROM all_source
  79.         where owner = v_owner
  80.         and name    = v_pkg_name
  81.         and type    = v_obj_type
  82.         and line    < v_line_nbr
  83.         ORDER  BY line
  84.       )
  85.    LOOP
  86.       v_stack := src.text;
  87.       IF it_is_comment THEN
  88.          v_pos1 :=  INSTR (v_stack, '*/');
  89.          IF v_pos1 > 0 THEN
  90.             v_stack := SUBSTR (v_stack, v_pos1 + 2);
  91.             it_is_comment := FALSE;
  92.          ELSE
  93.             v_stack := ' ';
  94.          END IF;
  95.       END IF;
  96.       --
  97.       IF v_stack != ' ' THEN
  98.       --
  99.          v_pos1 := INSTR (v_stack, '/*');
  100.          WHILE v_pos1 > 0 LOOP
  101.             v_tmp_1 := SUBSTR (v_stack, 1, v_pos1 - 1);
  102.             v_pos2 := INSTR (v_stack, '*/');
  103.             IF v_pos2 > 0 THEN
  104.                v_tmp_2 := SUBSTR (v_stack, v_pos2 + 2);
  105.                v_stack := v_tmp_1||v_tmp_2;
  106.             ELSE
  107.                v_stack := v_tmp_1;
  108.                it_is_comment := TRUE;
  109.             END IF;
  110.             v_pos1 := INSTR (v_stack, '/*');
  111.          END LOOP;
  112.          --
  113.          IF v_stack != ' ' THEN
  114.             v_pos1 := INSTR (v_stack, '--');
  115.             IF v_pos1 > 0 THEN
  116.                v_stack := SUBSTR (v_stack, 1, v_pos1 - 1);
  117.             END IF;
  118.             --
  119.             IF v_stack != ' ' THEN
  120.                FOR v_idx in v_literal_arr.FIRST .. v_literal_arr.LAST
  121.                LOOP
  122.                   v_pos1 := INSTR (v_stack, v_literal_arr (v_idx) );
  123.                   WHILE v_pos1 > 0  LOOP
  124.                      v_pos2 := INSTR (v_stack, v_literal_arr (v_idx), v_pos1 + 1);
  125.                      IF v_pos2 > 0 THEN
  126.                         v_tmp_1 := SUBSTR (v_stack, 1, v_pos1 - 1);
  127.                         v_tmp_2 := SUBSTR (v_stack, v_pos2 + 1);
  128.                         v_stack := v_tmp_1||v_tmp_2;
  129.                      ELSE
  130.                         IF it_is_literal THEN
  131.                            v_stack := SUBSTR (v_stack, v_pos1 + 1);
  132.                            it_is_literal := FALSE;
  133.                         ELSE
  134.                            v_stack := SUBSTR (v_stack, 1, v_pos1 - 1);
  135.                            it_is_literal := TRUE;
  136.                         END IF;
  137.                      END IF;
  138.                      v_pos1 := INSTR (v_stack, v_literal_arr (v_idx) );
  139.                   END LOOP;
  140.                END LOOP;
  141.                --
  142.                IF v_stack != ' ' THEN
  143.                   WHILE INSTR (v_stack, '  ') > 0
  144.                   LOOP
  145.                      v_stack := REPLACE(v_stack, '  ', ' ');
  146.                   END LOOP;
  147.                   v_stack := REPLACE(v_stack, ' END IF ', ' END ');
  148.                   v_stack := REPLACE(v_stack, ' END LOOP ', ' END ');
  149.                   --
  150.                   IF v_stack != ' ' THEN
  151.                      v_stack := ' '||v_stack;
  152.                      v_pos1 := INSTR(v_stack, ' FUNCTION ') + INSTR(v_stack, ' PROCEDURE ');
  153.                      IF v_pos1 > 0 THEN
  154.                         v_obj_type := TRIM(SUBSTR(v_stack, v_pos1 + 1, 9));  -- get object type
  155.                         v_stack := TRIM(SUBSTR(v_stack, v_pos1 + 10))||'  ';  -- cut object type
  156.                         v_stack := SUBSTR(v_stack, 1,  INSTR(v_stack, ' ') - 1 );  -- get object name
  157.                         v_tbl.EXTEND(1);
  158.                         v_tbl (v_tbl.last) := v_obj_type||' '||v_owner||'.'||v_pkg_name||'.'||v_stack;
  159.                      END IF;
  160.                   --
  161.                      v_pos1 := 0;
  162.                      v_pos2 := 0;
  163.                      v_tmp_1 := v_stack;
  164.                      v_tmp_2 := v_stack;
  165.                      FOR v_idx in v_blk_bgn_tbl.FIRST .. v_blk_bgn_tbl.LAST
  166.                      LOOP
  167.                         v_str_len := NVL(LENGTH(v_tmp_1),0);
  168.                         v_tmp_1 := REPLACE(v_tmp_1,v_blk_bgn_tbl(v_idx), NULL);
  169.                         v_bgn_cnt := NVL(LENGTH(v_tmp_1), 0);
  170.                         v_pos1 := v_pos1 + (v_str_len - v_bgn_cnt)/v_blk_bgn_len_tbl(v_idx);
  171.                         v_str_len := NVL(LENGTH(v_tmp_2),0);
  172.                         v_tmp_2 := REPLACE(v_tmp_2,' END ', NULL);
  173.                         v_end_cnt := NVL(LENGTH(v_tmp_2), 0);
  174.                         v_pos2 := v_pos2 + (v_str_len - v_end_cnt)/5; --- 5 is the length(' END ') 
  175.                      END LOOP;
  176.                      IF v_pos1 > v_pos2 THEN
  177.                         v_blk_cnt := v_blk_cnt + 1;
  178.                      ELSIF v_pos1 < v_pos2 THEN
  179.                         v_blk_cnt := v_blk_cnt - 1;
  180.                         IF v_blk_cnt = 0 AND v_tbl.COUNT > 0 THEN
  181.                            v_tbl.DELETE(v_tbl.last);
  182.                         END IF;
  183.                      END IF;
  184.                   END IF;
  185.                END IF;
  186.             END IF;
  187.          END IF;
  188.       END IF;
  189.    END LOOP;
  191.    RETURN CASE v_tbl.COUNT WHEN 0 THEN 'UNKNOWN' ELSE v_tbl(v_tbl.LAST) END;
  193. END;
  194. /

测试：

  1. SQL> create or replace package body pkg_test as
  2.   2    procedure p_test_in_pkg is
  3.   3    begin
  4.   4      dbms_output.put_line('Packaged procedure, using $$plsql_unit = ' || $$plsql_unit);
  5.   5      dbms_output.put_line('FN_WHO_AM_I = ' || fn_who_am_i);
  6.   6      null;
  7.   7    end;
  8.   8  end;
  9.   9  /
  11. Package body created.
  13. SQL> exec pkg_test.p_test_in_pkg;
  14. Packaged procedure, using $$plsql_unit = PKG_TEST        --> this one is wrong (package body name) ...
  15. FN_WHO_AM_I = PROCEDURE SCOTT.PKG_TEST.P_TEST_IN_PKG     --> ... but this one is OK
  17. PL/SQL procedure successfully completed.
  19. SQL>
展开查看全部
赞(0）分享  回复(0）举报  2023-04-29
jgovgodb

jgovgodb3#

一种方法使用从这里借用的想法：http://www.orafaq.com/forum/t/173023/

注意：这只适用于Oracle 12c。对于11g，它只给出了包名称。

创建一个名为show_caller的过程。这使用了OWA_UTIL.WHO_CALLED_ME过程。

  1. create or replace procedure show_caller
  2. as
  3.    l_owner  varchar2(200);
  4.    l_name   varchar2(200); 
  5.    l_lineno number;
  6.    l_caller varchar2(200);
  7. begin 
  8.    OWA_UTIL.WHO_CALLED_ME (l_owner, l_name,l_lineno,l_caller);
  9.    dbms_output.put_line('Inside '||l_name);
  11. end;
  12. /

现在，你可以把你的包写成：

  1. CREATE OR replace PACKAGE test_pkg 
  2. AS 
  3.   PROCEDURE proc1; 
  4. END test_pkg; 
  6. / 
  7. CREATE OR replace PACKAGE BODY test_pkg 
  8. AS 
  9.   PROCEDURE Proc1 
  10.   AS 
  11.   BEGIN 
  12.       show_caller;  -- proc1 calls show_caller 
  13.   END proc1; 
  14. END test_pkg; 
  16. /

处决

  1. SET SERVEROUTPUT ON
  2. BEGIN
  3.    Test_pkg.Proc1;
  4. END;
  5. /
  6. Inside TEST_PKG.PROC1

请注意，这将打印过程的名称。如果要将其作为变量使用，则将l_name（如果需要，还可以与其他变量沿着）作为OUT变量从show_caller传递
Live SQL Demo（需要免费OTN帐户）
另一种选择是在过程中使用OWA_UTIL.GET_PROCEDURE函数：但在这种情况下似乎不适合我我很乐意从Maven那里了解更多。

展开查看全部
赞(0）分享  回复(0）举报  2023-04-29
suzh9iv8

suzh9iv84#

我的快速样本：

  1. create or replace package body MyPackage as
  3.     procedure PrintMethodName is
  4.         ThisMethod#FullName varchar2(128) := regexp_substr(
  5.             dbms_utility.format_call_stack, 
  6.             'package\s+body\s+([^[:space:]]+)',
  7.             1,1,'i',1); -- schema.package.method
  8.         ThisMethod#CurrentSchemaName varchar2(128) := regexp_substr(
  9.             regexp_substr(
  10.                 dbms_utility.format_call_stack, 
  11.                 'package\s+body\s+([^[:space:]]+)',
  12.                 1,1,'i',1), 
  13.             '[^.]+[.][^.]+$'); -- package.method
  14.         ThisMethod#MethodName varchar2(128) := regexp_substr(
  15.             regexp_substr(
  16.                 dbms_utility.format_call_stack, 
  17.                 'package\s+body\s+([^[:space:]]+)',
  18.                 1,1,'i',1), 
  19.             '[^.]+$'); -- method
  20.     begin
  21.         dbms_output.put_line(dbms_utility.format_call_stack);
  22.         dbms_output.put_line(ThisMethod#FullName);
  23.         dbms_output.put_line(ThisMethod#CurrentSchemaName);
  24.         dbms_output.put_line(ThisMethod#MethodName);
  25.     end;
  27. end;
  28. /
展开查看全部
赞(0）分享  回复(0）举报  2023-04-29
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com