Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208

    Unanswered: determine the calling procedure

    Is there a way inside a proc to determine what called the current procedure without passing that info?

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    do you mean what user?

    select user from dual;
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208
    I mean the actual procedure.

    ie Proc1 calls Proc2 and Proc3 can call Proc2

    Inside Proc2 I would like to determine what proc called it.

    Is there a dictionary table that stores the Stack, or some kind of pl/sql attribute that tells where the procedure was called from?

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    you could select from the dictionary to determine
    what procs have a call to that procedure.

    example:
    PHP Code:
    select  OWNERNAME
    from ALL_SOURCE 
    where 
    TEXT like 
    '%Proc2%'
    order by LINE 

    or specify OWNER or other definitions
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    hi,

    if u have access to the code of the procedures, u might want to add a parameter to the parameterlist of the procedure to store the name of the called procedure. When calling it, u can give the name of the calling procedure.

    If u don't have access or it is not possible for some other reason, then i don't know of a view to show the called procedure. U might want to take a look at the statemens issued by the user calling the procedure to see if there's any call displayed, but i doubt it.

    Hope this helps.
    Edwin van Hattem
    OCP DBA / System analyst

  6. #6
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208
    Actually I found what I was looking for thx.

    There is a DBMS package for formating the stack. Here is the procedure I use.

    CREATE OR REPLACE FUNCTION Get_Calling_Procedure(
    p_depth IN PLS_INTEGER := 2)
    RETURN VARCHAR2
    IS
    lv_start PLS_INTEGER;
    lv_stop PLS_INTEGER;
    lv_stk VARCHAR2(2000) := LOWER(DBMS_UTILITY.FORMAT_CALL_STACK);
    lv_stack varchar2(200);
    lv_text varchar2(32);
    BEGIN

    lv_start := INSTR(lv_stk,CHR(10),1,p_depth + 2) + 11;
    lv_stop := INSTR(lv_stk,CHR(10),1,p_depth + 3);

    lv_stack := SUBSTR(lv_stk, lv_start,lv_stop - lv_start);

    return UPPER(substr(lv_stack,(instr(lv_stack,'.')+1),leng th(lv_stack)));

    END;
    /

  7. #7
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    hi,

    tnx for sharing, i didn't know that.

    greetz
    Edwin van Hattem
    OCP DBA / System analyst

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •