Get name of nested procedure that called my procedure

Question

I am new to PL SQL and I want to know is there a way (method/function/etc) that I can get the name of nested procedure that called my procedure?

For example, I have my_procedure and it gets called by another_procedure nested in another_package. I want a funciton/method implemented in my_procedure that tells me every time which nested procedure called my_procedure, which is, in this example, my_procedure.

I am using owa_util.who_called_me for getting the package and owner name.

The `name` out argument from `owa_util.who_called_me` tells you the package and procedure name that called; so I'm not sure quote what you're looking for? Maybe an [mcve] could help. — Alex Poole, Jun 18 '21 at 11:44
No, it considers the outermost object only bcz it's a nested procedure. So for my example, I get owner, package, line in package from where it's called and 'package body'. — OmarLittle, Jun 18 '21 at 12:21
OK, you're on an older version then; [18c shows both](https://dbfiddle.uk/?rdbms=oracle_18&fiddle=a7777a9d1f13e32a55cb419dddc6a182), [11g doesn't](https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=a7777a9d1f13e32a55cb419dddc6a182). Which version are you actually using - it may affect what else is available? — Alex Poole, Jun 18 '21 at 12:31
This answer includes how to get the calling procedure: https://stackoverflow.com/a/50541599/230471 I'm not sure what you mean by a nested procedure though. — William Robertson, Jun 19 '21 at 14:25
I am new to DB-s and PL SQL but I believe that a nested procedure is a procedure defined in another package/function/procedure. Hence the word 'nested'. — OmarLittle, Jun 21 '21 at 12:06

score 2 · Accepted Answer · answered Jun 19 '21 at 14:59

Demo procedure:

create or replace procedure demo
as
    k_this   constant varchar2(300) := utl_call_stack.concatenate_subprogram(utl_call_stack.subprogram(1));
    k_caller constant varchar2(300) := utl_call_stack.concatenate_subprogram(utl_call_stack.subprogram(2));
begin
    dbms_output.put_line(k_this || ' called from ' || k_caller);
end demo;

Package to call it:

create or replace package testit
as
    procedure do_something;
end testit;
/

create or replace package body testit
as
    procedure do_something is
    begin
        demo;
    end do_something;
end testit;
/

Test:

begin
    testit.do_something;
end;
/

DEMO called from TESTIT.DO_SOMETHING

Get name of nested procedure that called my procedure

1 Answers1