ALTER SESSION SET plscope_settings='identifiers:all'

create or replace package Test_Package_Func_Proc   
as   
 function  test_func  (in_val in number) return number;   
 procedure test_proc (in_val in number);   
end; 

create or replace package body Test_Package_Func_Proc   
as   
 global_var number := 0;  
 function test_func (in_val in number) return number    
 is   
 begin   
 return in_val;   
 exception   
  when others then   
    RAISE;   
 end;   
   
 procedure test_proc  (in_val in number)   
 is   
   procedure submodule( in_val in number) is  
   begin  
      dbms_output.put_line (in_val);   
   end;    
 begin   
   submodule(in_val);   
 exception   
  when others then   
    RAISE;   
 end;   
begin  
  global_var := 1;  
end;  

identify_module

create or replace function identify_module (p_owner in varchar2, p_unit in varchar2, p_line in number) return varchar2    
  authid definer    
is    
  /**************************************************************   
  /* Name          :  identify_module   
  **************************************************************   
  * description    :  uses PLSCOPE, to get additional info about the module name of a source code line    
  *  This only works reliably for code compiled with optimization level = 1   
  *  higher optimization levels might move code lines. The line reported in error and backtrace stacks (run time) can differ from the lines stored in PL/scope or user_source (compile time).   
  *   
  * @author: Sven Weller, syntegris information solutions GmbH   
  **************************************************************   
  * parameter         
  * @param  : p_owner = schema name of unit   
  * @param  : p_unit = package name   
  * @param  : p_line = line of code, for which we would like to see the name of the modul   
  * @return : concatenated submodule names     
  **************************************************************/   
  cursor c_search_by_line (cv_owner in varchar2, cv_unit in varchar2, cv_line in number)  
  is  
    select /*+ first_rows(1) */ i.type, i.name, i.line, i.usage_id, i.usage_context_id, i.usage, i.signature  
    from user_IDENTIFIERS i  
    where --i.owner = cv_owner  
        i.object_name = cv_unit  
    and i.object_type = 'PACKAGE BODY'  
    and i.line <= cv_line   
    -- context must be in same package body  
    --and exists (select null from ALL_IDENTIFIERS i2 where i.usage_context_id = i2.usage_id and i2.object_name = cv_unit and i2.object_type = 'PACKAGE BODY')  
    and i.usage_context_id in (select i2.usage_id from user_IDENTIFIERS i2 where i2.object_name = cv_unit and i2.object_type = 'PACKAGE BODY')  
    order by --decode (usage, 'DEFINITION',1, 'DECLARATION',2,3),   
              line desc, usage_id asc  
    ;  
    
  cursor c_search_by_usage (cv_owner in varchar2, cv_unit in varchar2, cv_usage_id in number)  
  is  
    select /*+ first_rows(1) */ type, name, line, usage_id, usage_context_id, usage, signature  
    from user_IDENTIFIERS  
    where -- owner = cv_owner  
        object_name = cv_unit  
    and OBJECT_TYPE = 'PACKAGE BODY'  
    and usage_id = cv_usage_id   
    order by decode (usage, 'DEFINITION',1, 'DECLARATION',2,3), line desc, usage_id asc  
    ;  
  
  r_result_byLine  c_search_by_line%rowtype;  
  r_result_byUsage c_search_by_usage%rowtype;  
  r_last_result    c_search_by_usage%rowtype; 
  v_owner          varchar2(128);  
  v_modul_name     user_identifiers.name%type;  
  v_first_type     user_identifiers.type%type;  
  v_max_hierarchy  binary_integer := 5;  
begin  
  -- If owner is missing, use the current schema  
  v_owner := coalesce(p_owner,sys_context('userenv','current_schema'));  
    
  -- find the closest line and check its context.  
  open c_search_by_line(v_owner, p_unit, p_line);  
  fetch c_search_by_line into r_result_byLine;  
  close c_search_by_line;  
  
  if r_result_byLine.usage_context_id = 1 then  
    -- we seem to be already in main package body.  
    -- this can be either a problem during a parameter call   
    -- or the error happened in the initialisatzion part of the package  
    case r_result_byLine.usage   
      when 'DEFINITION' then  
        v_modul_name :=r_result_byLine.type||' '||p_unit||'.'||r_result_byLine.name;  
      when 'DECLARATION' then  
        v_modul_name :='declaration of '||r_result_byLine.type||' '||p_unit||'.'||r_result_byLine.name;  
      else   
        v_modul_name :='body of '||p_unit;  
    end case;  
      
  else      
      r_result_byUsage := r_result_byLine;  
      --r_result_byUsage.usage_context_id := r_result_byLine.usage_id;  
        
      -- find module names  
      <<parent_modules>>  
      loop   
        if r_result_byUsage.usage in ('DEFINITION', 'DECLARATION')    
           and r_result_byUsage.type in ('PROCEDURE','FUNCTION')  
           and (r_last_result.signature != r_result_byUsage.signature or r_last_result.signature is null) 
           then  
             -- concat multiple submodule names  
             v_modul_name := r_result_byUsage.name  
                            ||case when v_modul_name is not null then '.'||v_modul_name end;  
             v_first_type := coalesce(v_first_type, r_result_byUsage.type);  
             -- remember result to compare if we get duplicate entries because of declaration->definition 
             r_last_result := r_result_byUsage; 
        end if;  
        -- stop when package body level is reached  
        exit when r_result_byUsage.usage_context_id in (0, 1) or v_max_hierarchy = 0;  
  
  
        -- it seems to be a submodule, so do an additional call and fetch also the parent module  
        open c_search_by_usage(p_owner, p_unit, r_result_byUsage.usage_context_id);  
        fetch c_search_by_usage into r_result_byUsage;  
        close c_search_by_usage;  
      
          
        -- safety counter to prevent endless loops  
        v_max_hierarchy := v_max_hierarchy - 1;  
      end loop parent_modules;    
      
      -- add info about type (FUNCTION/PROCEDURE)  
      if v_modul_name is not null then  
        v_modul_name :=v_first_type||' '||p_unit||'.'||v_modul_name;  
      --else     
      --  v_modul_name := '--no submodule found--';  
      end if;  
  end if;    
  return v_modul_name;  
exception  
  when no_data_found then  
    return null;  
end identify_module;

select line, identify_module(user, name, line) , text   
from user_source  
where name='TEST_PACKAGE_FUNC_PROC'  
and type = 'PACKAGE BODY' 
order by line