Pl/SQL Nested Procedure Exception Handling

Question

This is a best practice question on error handling through multiple levels of PL/SQL procedures. I've looked at a few other questions to help me out, in particular this one.

Currently, I have a program with Procedure 1, which calls Procedure 2, which calls Procedure 3. I'm trying to perform adequate error handling - but I'd like to output eventually the exact problem back to the application layer. I'm hoping to get some ideas on how I can do this efficiently and clearly.

My current solution method is below, but it seems rather messy to me, with lots of variable declarations. I am very new to PL/SQL (and SQL in general) so I'd appreciate any advice on:

1. Good error handling techniques when dealing with multiple layers of procedures.
2. Feeding error messages back up to application layer (in my procedure below, represented by "out_overall_output" variable.

Program Flow: UI -> Proc 1 -> Proc 2 -> Proc 3

Procedure 1:

--One input variable, one output.
in_id                VARCHAR2;
out_overall_output   VARCHAR2;
...
DECLARE
    l_success         BOOLEAN;
    l_error_output    VARCHAR2(100);
BEGIN
    Proc2(id, l_success, l_error_output);
    IF l_success = FALSE THEN
        out_overall_output = l_error_output
    END IF
EXCEPTION
WHEN OTHERS THEN
    ROLLBACK;
    out_overall_output:= 'Error calling Proc 2'
    RETURN;
END;
--Normal flow continues if l_success is true...

Procedure 2:

-- One input variable, two output.
in_id
out_success
out_error_output
//other logic
DECLARE
    l_success         BOOLEAN;
    l_error_output    VARCHAR2(100)
BEGIN
    Proc3(id, l_success, l_error_output)
    IF l_success = FALSE THEN
        out_error_output = l_error_output
    END IF
EXCEPTION
WHEN OTHERS
    out_error_output = 'Error calling Proc 3'
    RETURN;
END;

Procedure 3:

--One input variable, two output.
in_id                VARCHAR2;
out_success          BOOLEAN;
out_error_message    VARCHAR2;
...
BEGIN
    DELETE 
    FROM table
    WHERE id = in_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
    out_success = FALSE;
    out_error_message = 'Error - No data to delete'
WHEN OTHERS THEN
    out_success = FALSE;
    out_error_message = 'Error deleting data.'
END;

Note: The levels of procedure calling goes deeper than this. The snippets I have shown are greatly simplified. The error messages and variable names in my real procedures are more descriptive.

Answer 1

To show exact explanations of "what happens with a server" for application level you can try following. In procedures:

create or replace procedure p1 is
...
exception
  when <some_error> then
    <do something>
    -- re-raise error:
    raise_application_error(-20001, 'Client with ID '|| ID || ' has no right to perform action "' || ACTION_NAME || '"', true);
end;

create or replace procedure p2 is
begin
  p1;
exception
  when <another_error> then
    <do something>
    -- re-raise error:
    raise_application_error(-20002, 'Action "' || ACTION_NAME || '" is not completed', true);
end;

create or replace procedure p3 is
begin
  p2;
exception
  when <another_error> then
    <do something>
    -- re-raise error:
    raise_application_error(-20003, 'Purchasing of "' || CAR_NAME || '" cancelled', true);
end;

And in top level procedure:

create or replace procedure top_level_procedure is
begin
  p1;
exception
  when <one_more_error> then
    <do something>
    raise_application_error(-20004, dbms_utility.format_error_backtrace);
end;

After exception in p1 you will see something like this:

ORA-20003: Purchasing of "Cool red Ferrari" cancelled
ORA-20002: Action "car purchase" is not completed
ORA-20001: Client with ID 123 has no right to perform action "Spent all money of Bill Gates"

Third parameter of procedure raise_application_error with false value cuts all previous error messages. If you will use false value in procedure p3, you will see only one error message with code ORA-20003 in this example.

P. S. Also you can define your own exceptions and use them in WHEN .. THEN clause. Here you find more information and examples: https://docs.oracle.com/cd/B28359_01/appdev.111/b28370/errors.htm#LNPLS00704

P. P. S. How to log. Log procedure:

create or replace procedure log(p_log_message varchar2) is
pragma autonomous_transaction;
begin
  insert into log_table(..., log_message) values (..., p_log_message);
  commit;
end;

Call log procedure:

  when <one_more_error> then
    <do something>
    log(..., dbms_utility.format_error_backtrace);
    raise_application_error(-20004, dbms_utility.format_error_backtrace);