alter session set plscope_settings='identifiers:all, statements:all'
/
create or replace function f ( p int ) 
  return int as
begin
  return p + 1;
end f;
/

create or replace procedure p ( param int ) as
  val int;
begin
  select f(param)
  into   val
  from   dual;
  
  dbms_output.put_line(val);
end p;
/

exec p(1);

with my_prog_unit as (
  select user owner, 'P' object_name from dual
), full_set as (
  select ai.usage,
         ai.usage_id,
         ai.usage_context_id,
         ai.type,
         ai.name
  from   all_identifiers ai, my_prog_unit
  where  ai.object_name = my_prog_unit.object_name
  and    ai.owner = my_prog_unit.owner
  union all
  select st.type,
         st.usage_id,
         st.usage_context_id,
         'TYPE',
         'NAME'
  from   all_statements st, my_prog_unit
  where  st.object_name = my_prog_unit.object_name
  and    st.owner = my_prog_unit.owner
), dml_statements as (
  select st.owner, st.object_name, st.line, st.usage_id, st.type
  from   all_statements st, my_prog_unit
  where  st.object_name = my_prog_unit.object_name
  and st.owner = my_prog_unit.owner
  and st.type in ('SELECT', 'UPDATE', 'DELETE')
)
  select st.owner,
         st.object_name,
         st.line,
         st.type,
         s.text
  from   dml_statements st, all_source s
  where ('CALL', 'FUNCTION') in (
    select fs.usage, fs.type
    from   full_set fs
    connect by prior fs.usage_id = fs.usage_context_id
    start with fs.usage_id = st.usage_id
  )
  and st.line = s.line
  and st.object_name = s.name
  and st.owner = s.owner;

OWNER   OBJECT_NAME   LINE   TYPE     TEXT                 
CHRIS   P                  4 SELECT     select f(param)