Multiple functions calling a same function. I need to create a flag

Multiple functions calling a same function. I need to create a flag. Suppose I have 4 functions(f1,f2,f3 and f4) in a PLSQL package. F1 is being called by F2,F3 and F4. All I want a flag in order to track which function call the F1. For example . If f2 called f1 then flag=2,flag=3 when f3 called f1 and so on and this should be accessible in such way that I can this flag inside function F1.

Answer

You don’t need to pass any flag, as PL/SQL can tell you the call stack.

create or replace package demo
as
    function f1 return number;
    function f2 return number;
    function f3 return number;
    function f4 return number;
end demo;

create or replace package body demo
as

    function f1 return number
    is
        this_unit varchar2(257) := utl_call_stack.concatenate_subprogram(utl_call_stack.subprogram(1));
        caller    varchar2(257) := utl_call_stack.concatenate_subprogram(utl_call_stack.subprogram(2));
    begin
        dbms_output.put_line(this_unit || ' called from ' || caller);
        return 1;
    end f1;

    function f2 return number
    is
        this_unit varchar2(257) := utl_call_stack.concatenate_subprogram(utl_call_stack.subprogram(1));
        caller    varchar2(257) := utl_call_stack.concatenate_subprogram(utl_call_stack.subprogram(2));
    begin
        dbms_output.put_line(this_unit || ' called from ' || caller);
        return f1 * 2;
    end f2;

    function f3 return number
    is
        this_unit varchar2(257) := utl_call_stack.concatenate_subprogram(utl_call_stack.subprogram(1));
        caller    varchar2(257) := utl_call_stack.concatenate_subprogram(utl_call_stack.subprogram(2));
    begin
        dbms_output.put_line(this_unit || ' called from ' || caller);
        return f1 + f2;
    end f3;

    function f4 return number
    is
        this_unit varchar2(257) := utl_call_stack.concatenate_subprogram(utl_call_stack.subprogram(1));
        caller    varchar2(257) := utl_call_stack.concatenate_subprogram(utl_call_stack.subprogram(2));
    begin
        dbms_output.put_line(this_unit || ' called from ' || caller);
        return f2 * 2;
    end f4;

end demo;

Test:

declare
    n number;
begin
    n := demo.f3;
end;
/

DEMO.F3 called from __anonymous_block
DEMO.F1 called from DEMO.F3
DEMO.F2 called from DEMO.F3
DEMO.F1 called from DEMO.F2

You can adjust the calls to util_call_stack to exclude the package name if you only want the function names.