Hello, Tom.
I my project we have a package with a pretty standard structure, I think. It has public procedures calling in turn private procedures, calling innermost procedures eventually. Simplifying, something like this:
-----------------------------------------------------
create or replace PACKAGE body pck_proccess is
-----------------------------------------------------
procedure prc_lvl_3(p_in_par) as
-- operations
end prc_lvl_3;
-----------------------------------------------------
procedure prc_lvl_2(p_in_par) as
prc_lvl_3(oper_params);
-- operations
end prc_lvl_2;
-----------------------------------------------------
procedure prc_lvl_1(p_in_par) as
prc_lvl_2(oper_params);
-- operations
end prc_lvl_1;
-----------------------------------------------------
end pck_proccess;
-----------------------------------------------------
This package must call a logging procedure at specific stages of processing and in case of foreseen and unforeseen (like ORA-XXXX exceptions) errors.
The last might, of course, occur, during the 'innermost' procedures/functions execution.
An exception in the procedure prc_lvl_3 should stop the flow of prc_lvl_2, the one in prc_lvl_2 - stops prc_lvl_1.
Besides, in case of ORA-exception (unforeseen, having occurred whilst one of innermost procedures), the log message must consist of SQLERRM and the string of all input parameters of the procedure which triggered an exception.
Taking it all into account, I found a solution that is as follows:

-----------------------------------------------------
create or replace PACKAGE body pck_proccess is
-----------------------------------------------------
e_internal_error EXCEPTION;
pragma exception_init(e_internal_error,-20001);

procedure prc_lvl_3(p_in_par) as
-- operations
exception when others then
pck_logging.prc_log_msg(log_param1, log_param2);
raise_application_error(-20001, sqlerrm);
end prc_lvl_3;
-----------------------------------------------------
procedure prc_lvl_2(p_in_par) as
prc_lvl_3(oper_params);
-- operations
-----------------------------------------------------
exception when e_internal_error then
raise_application_error(-20001, sqlerrm);
-----------------------------------------------------
exception when others then
pck_logging.prc_log_msg(log_param1, log_param2);
raise_application_error(-20001, sqlerrm);
-----------------------------------------------------
end prc_lvl_2;
-----------------------------------------------------
procedure prc_lvl_1(p_in_par) as
prc_lvl_2(oper_params);
-- operations
-----------------------------------------------------
exception when e_internal_error then
raise_application_error(-20001, sqlerrm);
-----------------------------------------------------
exception when others then
pck_logging.prc_log_msg(log_param1, log_param2);
raise_application_error(-20001, sqlerrm);
-----------------------------------------------------
end prc_lvl_1;
end pck_proccess;

-----------------------------------------------------
Basically, if we have already handle the exception, we pass it upper - so the rest procedures 'know' that the processing must be stopped, but log message has already be written.


Now to the question.
1) Can you advise me a better way to do this handling and logging?
2) My boss considers this way too heavy and ‘expensive’; because we pass an exception to the ‘top’ up to 3 times (there are 4 levels of 'nesting'). He suggests that we use a global variable (something like v_error_count) to be used as a flag pointing, whether something went wrong... I don't like global vars, so I am thinking of an out parameter p_out_status for every procedure, acting like a flag as well, but I like this idea neither.
So the question is, are exceptions really so hard to raise (in terms of performance), so that we need to search for some ways (seeming tricky enough) to handle them without passing it to the top?


Thank you very much in advance.

create or replace procedure show_trace is
begin
  dbms_output.put_line( dbms_utility.format_error_backtrace );  
end show_trace;
/
      
create or replace package pkg as
  procedure p1;
  procedure p2;
  procedure p3;
end pkg;
/

create or replace package body pkg as
  procedure p1 as
    n number;
  begin
    n := 1/0;
  end p1;
  
  procedure p2 as
  begin
    p1;
  end p2;
  
  procedure p3 as
  begin
    p2;
  exception
    when others then 
      show_trace;
      raise;
  end p3;
end pkg;
/

exec pkg.p3;

Error report -
ORA-01476: divisor is equal to zero
ORA-06512: at "CHRIS.PKG", line 19
ORA-06512: at line 1
01476. 00000 -  "divisor is equal to zero"
*Cause:    
*Action:
ORA-06512: at "CHRIS.PKG", line 5
ORA-06512: at "CHRIS.PKG", line 10
ORA-06512: at "CHRIS.PKG", line 15

create or replace procedure show_trace is
begin
  for i in reverse 1 .. utl_call_stack.backtrace_depth loop
    dbms_output.put_line (
      'Line: ' || utl_call_stack.backtrace_line(i) || ', ' || 
      'Subprogram: ' || utl_call_stack.backtrace_unit(i)
    );
  end loop;
end show_trace;
/

exec pkg.p3;

Error report -
ORA-01476: divisor is equal to zero
ORA-06512: at "CHRIS.PKG", line 19
ORA-06512: at line 1
01476. 00000 -  "divisor is equal to zero"
*Cause:    
*Action:
Line: 5, Subprogram: CHRIS.PKG
Line: 10, Subprogram: CHRIS.PKG
Line: 15, Subprogram: CHRIS.PKG

alter session set plsql_optimize_level = 3;

alter package pkg compile body;

exec pkg.p3;

Error report -
ORA-01476: divisor is equal to zero
ORA-06512: at "CHRIS.PKG", line 19
ORA-06512: at line 1
01476. 00000 -  "divisor is equal to zero"
*Cause:    
*Action:
Line: 13, Subprogram: CHRIS.PKG