This discussion is archived
7 Replies Latest reply: Jan 11, 2013 10:45 AM by rp0428 RSS

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

Tomeo Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    Thanks - brain f*** - didn't even see your post somehow. I update my post with the old method.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points