找出PL / SQL过程的名称

Can PL/SQL procedure in Oracle know it's own name?

Oracle中的PL / SQL程序能否知道它自己的名字?

Let me explain:

让我解释:

CREATE OR REPLACE procedure some_procedure is
    v_procedure_name varchar2(32);
begin
    v_procedure_name := %%something%%;
end;

After %%something%% executes, variable v_procedure_name should contain 'SOME_PROCEDURE'. It is also OK if it contains object_id of that procedure, so I can look up name in all_objects.

在%%执行%%之后,变量v_procedure_name应包含'SOME_PROCEDURE'。如果它包含该过程的object_id也可以,所以我可以在all_objects中查找名称。

4 个解决方案

#1

Try:

v_procedure_name := $$PLSQL_UNIT;

There's also $$PLSQL_LINE if you want to know which line number you are on.

如果你想知道你在哪个行号,还有$$ PLSQL_LINE。

#2

If you are pre-10g, you can 'dig' (parse) it out of dbms_utility.format_call_stack Procedures/functions in packages can be overloaded (and nested), so the package name/line number is normally better than the name.

如果你是10g之前,你可以从dbms_utility.format_call_stack中“挖掘”(解析)它包中的过程/函数可以重载(和嵌套),因此包名/行号通常比名称更好。

#3

In 10g and 11g I use the "owa_util.get_procedure" function. I normally use this in packages as it will also return the name of an internal procedure or function as part of the package name, i.e. (package_name).(procedure name). I use this to provide a generic EXCEPTION template for identifying where an exception occured.

在10g和11g中我使用“owa_util.get_procedure”函数。我通常在包中使用它,因为它还会返回内部过程或函数的名称作为包名称的一部分,即(package_name)。(过程名称)。我用它来提供一个通用的EXCEPTION模板,用于识别发生异常的位置。

CREATE OR REPLACE procedure some_procedure is
    v_procedure_name varchar2(32);
begin
    v_procedure_name := owa_util.get_procedure;
end;

CREATE OR REPLACE PACKAGE some_package
AS
    FUNCTION v_function_name
    RETURN DATE;
END;
/
CREATE OR REPLACE PACKAGE BODY some_package
AS
    FUNCTION v_function_name
    RETURN DATE
    IS
    BEGIN
        RETURN SYSDATE;
    EXCEPTION
        WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('ERROR IN '||owa_util.get_procedure);
            DBMS_OUTPUT.PUT_LINE(SQLERRM);
    END;
END;
/

#4

Here's a neat function that takes advantage of REGEXP_SUBSTR. I've tested it in a package (and it even works if another procedure in the package calls it):

这是一个利用REGEXP_SUBSTR的简洁功能。我已经在一个包中测试过它(如果包中的另一个过程调用它,它甚至可以工作):

FUNCTION SET_PROC RETURN VARCHAR2 IS
BEGIN
  RETURN NVL(REGEXP_SUBSTR(DBMS_UTILITY.FORMAT_CALL_STACK, 
             'procedure.+\.(.+)\s', 1,1,'i',1), 'UNDEFINED');
END SET_PROC;

#1