title: Pl/SQL Nested Procedure Exception Handling
body: <p>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 <a href="https://stackoverflow.com/questions/7360565/pl-sql-exception-handling-in-procedures">this one</a>.</p>
  
  <p>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.</p>
  
  <p>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:</p>
  
  <ol>
  <li>Good error handling techniques when dealing with multiple layers of procedures.</li>
  <li>Feeding error messages back up to application layer (in my procedure below, represented by "out_overall_output" variable.</li>
  </ol>
  
  <p>Program Flow:    UI -> Proc 1 -> Proc 2 -> Proc 3</p>
  
  <p><strong>Procedure 1:</strong>  </p>
  
  <pre><code>--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...
  </code></pre>
  
  <p><strong>Procedure 2:</strong></p>
  
  <pre><code>-- 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;
  </code></pre>
  
  <p><strong>Procedure 3:</strong>  </p>
  
  <pre><code>--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;
  </code></pre>
  
  <p>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.</p>