7 Replies Latest reply: Jan 11, 2013 12:45 PM by rp0428 RSS

    How to get a called procedure/function name within package?

    Tomeo
      Hi folks,

      is it possible to obtain a called procedure/function name within package?

      For a measuring and tracing purpose, I would like to store an info at the beginning of each procedure/function in package with timestamp + additional details if needed.

      For example:
      CREATE OR REPLACE PACKAGE BODY "TEST_PACKAGE" IS
         PROCEDURE proc_1 IS
         BEGIN
            api_log.trace_data(sysdate, 'START.' || ???????);
            
            ...
      
            api_log.trace_data(sysdate, 'END.' || ???????);
         END;
      
         PROCEDURE proc_2 IS
         BEGIN
            api_log.trace_data(sysdate, 'START.' || ???????);
            
            ....
            proc_1;
            .... 
      
            api_log.trace_data(sysdate, 'END.' || ???????);
         END;
      
          
      END; 
      I would like to replace "???????" with a function which would return a name of called procedure, so result of trace data after calling TEST_PACKAGE.proc_2 would be:
         11.1.2013 09:00:01    START.*TEST_PACKAGE.proc_2* 
         11.1.2013 09:00:01    START.*TEST_PACKAGE.proc_1*
         11.1.2013 09:00:01    END.*TEST_PACKAGE.proc_1*
         11.1.2013 09:00:01    END.*TEST_PACKAGE.proc_2*
      I tried to use "dbms_utility.format_call_stack" but it did not return the name of procedure/function.

      Many thanks,
      Tomas

      PS: I don't want to use an hardcoding
        • 1. Re: How to get a called procedure/function name within package?
          odie_63
          PS: I don't want to use an hardcoding
          Why?

          Do you have such a scenario that you don't know the procedure name you're trying to compile?

          Edited by: odie_63 on 11 janv. 2013 10:45
          • 2. Re: How to get a called procedure/function name within package?
            padders
            Simple answer is you can't - without generating some additional metadata for the package.

            One way to generate the metadata would appear to be PL/Scope in 11g but I haven't actually tried that to see how it pans out.
            • 3. Re: How to get a called procedure/function name within package?
              odie_63
              There's $$PLSQL_UNIT compiler directive but it doesn't reference subprograms in a package, only the package itself :
              SQL> create or replace package mypck is
                2    procedure myprc;
                3  end;
                4  /
               
              Package created
               
              SQL> 
              SQL> create or replace package body mypck is
                2    procedure myprc is
                3    begin
                4      dbms_output.put_line('I''m in '||$$PLSQL_UNIT);
                5    end;
                6  end;
                7  /
               
              Package body created
               
              SQL> exec mypck.myprc;
               
              I'm in MYPCK
               
              PL/SQL procedure successfully completed
               
              • 4. Re: How to get a called procedure/function name within package?
                bencol
                This might help you understand the problem:
                http://tkyte.blogspot.co.uk/2009/10/httpasktomoraclecomtkytewhocalledme.html

                Buit I usually hardcode the proc name into a variable and use that for logging.
                • 5. Re: How to get a called procedure/function name within package?
                  rp0428
                  You've posted enough to know that you need to provide your 4 digit Oracle version (result of SELECT * FROM V$VERSION).
                  >
                  is it possible to obtain a called procedure/function name within package?

                  For a measuring and tracing purpose, I would like to store an info at the beginning of each procedure/function in package with timestamp + additional details if needed.
                  >
                  I usually use this method

                  1. Create a SQL type for logging information
                  2. Put the package name into a constant in the package spec
                  3. Add a line to each procedure/function for the name.

                  Sample package spec
                       /******************************************************************************
                        * Constants and package variables
                        ******************************************************************************/
                            gc_pk_name               CONSTANT VARCHAR2(30) := 'PK_TEST';
                  Sample procedure code in package
                        PROCEDURE P_TEST_INIT
                        IS
                          c_proc_name CONSTANT VARCHAR2(80)  := 'P_TEST_INIT';
                          v_log_info  TYPE_LOG_INFO := TYPE_LOG_INFO(gc_pk_name, c_proc_name); -- create the log type instance
                  
                        BEGIN
                            NULL; -- code goes here
                        EXCEPTION
                        WHEN ??? THEN
                            v_log_info.log_code := SQLCODE;  -- add info to the log type
                            v_log_info.log_message := SQLERRM;
                            v_log_info.log_time    := SYSDATE;
                            pk_log.p_log_error(v_log_info);
                                                  raise;
                        END P_PK_TEST_INIT;
                  Sample SQL type
                  DROP TYPE TYPE_LOG_INFO;
                  
                  CREATE OR REPLACE TYPE TYPE_LOG_INFO AUTHID DEFINER AS OBJECT (
                  /* *****************************************************************************
                   *  NAME:      TYPE_LOG_INFO
                   *  PURPOSE:   Holds info used by PK_LOG package to log errors.
                   *
                   *             Using a TYPE instance keeps the procedures and functions
                   *             independent of the logging mechanism.
                   *
                   *             If new logging features are needed a SUB TYPE can be derived
                   *             from this base type to add the new functionality without
                   *             breaking any existing code.
                   *
                   *  REVISIONS:
                   *  Ver        Date        Author           Description
                   *
                   *   1.00      mm/dd/yyyy  me               Initial Version.
                   ******************************************************************************/
                      PACKAGE_NAME  VARCHAR2(80),
                      PROC_NAME     VARCHAR2(80),
                      STEP_NUMBER   NUMBER,
                      LOG_LEVEL   VARCHAR2(10),
                      LOG_CODE    NUMBER,
                      LOG_MESSAGE VARCHAR2(1024),
                      LOG_TIME    TIMESTAMP,
                      CONSTRUCTOR FUNCTION type_log_info (p_package_name IN VARCHAR2 DEFAULT 'Uninitialized',
                                                          p_proc_name IN VARCHAR2 DEFAULT 'Uninitialized',
                                                          p_step_number IN NUMBER DEFAULT 1,
                                                          p_LOG_level IN VARCHAR2 DEFAULT 'Uninit',
                                                          p_LOG_code IN NUMBER DEFAULT -1,
                                                          p_LOG_message IN VARCHAR2 DEFAULT 'Uninitialized',
                                                          p_LOG_time IN DATE DEFAULT SYSDATE)
                                  RETURN SELF AS RESULT
                    ) NOT FINAL;
                  /
                  
                  DROP TYPE BODY TYPE_LOG_INFO;
                  
                  CREATE OR REPLACE TYPE BODY TYPE_LOG_INFO IS
                      CONSTRUCTOR FUNCTION type_log_info (p_package_name IN VARCHAR2 DEFAULT 'Uninitialized',
                                                          p_proc_name IN VARCHAR2 DEFAULT 'Uninitialized',
                                                          p_step_number IN NUMBER DEFAULT 1,
                                                          p_LOG_level IN VARCHAR2 DEFAULT 'Uninit',
                                                          p_LOG_code IN NUMBER DEFAULT -1,
                                                          p_LOG_message IN VARCHAR2 DEFAULT 'Uninitialized',
                                                          p_LOG_time IN DATE DEFAULT SYSDATE)
                       RETURN SELF AS RESULT IS
                      BEGIN
                        self.package_name  := p_package_name;
                        self.proc_name     := p_proc_name;
                        self.step_number   := p_step_number;
                        self.LOG_level   := p_LOG_level;
                        self.LOG_code    := p_LOG_code;
                        self.LOG_message := p_LOG_message;
                        self.LOG_time    := p_LOG_time;
                        RETURN;
                      END;
                  END;
                  /
                  SHO ERR
                  Edited by: rp0428 on Jan 11, 2013 10:35 AM after 1st cup of coffee ;)
                  • 6. Re: How to get a called procedure/function name within package?
                    odie_63
                    rp0428 wrote:
                    You would want the $$PLSQL_UNIT directive.
                    And as shown in my post, this doesn't apply to subprograms in a package, which seems to be precisely what OP is after.
                    • 7. Re: How to get a called procedure/function name within package?
                      rp0428
                      Thanks - brain f*** - didn't even see your post somehow. I update my post with the old method.