title: raise_application_error exception handling in subprocedures
body: <p>I have multiple stored procedures calling multiple stored procedures in my database. To give a small example, I've constructed a fictionalised version of a few of them below. In my example, a Java program calls <code>calculate_bill</code>, which calls <code>calculate_commission</code>, which calls <code>update_record</code>.</p>
  
  <p>I'm hoping to get some advice on how best to propagate the error messages up the stack to the calling Java layer, so the user gets a precise error message corresponding to wherever the error has occurred.</p>
  
  <p>I'm really quite stuck on this. I've played around in my example with <code>raise_application_error</code> and just continually shuffling it up the stack. Is the way I'm doing it below remotely correct? Or is one raise_application_error in the relevant procedure enough, with no <code>pragma exception init</code> etc needed?</p>
  
  <p>To give an idea of what I mean, in the example below, if a user entered a number which corresponded to a record which couldn't be updated because it didn't exist, I'd like them to get the message:
  "Error calculating bill. Error calculating commission. No record exists to be updated" or something to that effect.</p>
  
  <p>So two questions:</p>
  
  <ol>
  <li>What is the best practice, most efficient, most tidy way to pass error messages up the stack for the end user in the application layer?  </li>
  <li>Does anyone have any suggestions on tidier output from the code, i.e. the best way to concatenate these errors to make them more meaningful? I'm really open to any suggestions on how to make this work best as I have absolutely no prior experience in this.</li>
  </ol>
  
  <p>Example:
  (Error in code):</p>
  
  <pre><code>-20000 : Error in top level procedure
  -20001 : Error in middle level procedure
  -20002 : Error in bottom level procedure
  </code></pre>
  
  <p><strong>Java code:</strong></p>
  
  <pre><code>try {
      // call calculate_bill
  exception (SQLException ex)
      // output oracle code and relevant message.
  </code></pre>
  
  <p><strong>Oracle code:</strong></p>
  
  <pre><code>create or replace procedure calculate_bill(in_num NUMBER) 
  is
  error_calculating_commission EXCEPTION;
  error_updating_record EXCEPTION;
  PRAGMA EXCEPTION_INIT (error_calculating_commission, -20001);
  PRAGMA EXCEPTION_INIT (error_updating_record , -20002);
  begin
      if in_num &gt; 2 then
          calculate_commission(in_num);
  else
      raise_application_error(-20000, 'Error calculating bill. ' || 'Record number doesn''t exist.', false);
  end if;
  exception
      when error_calculating_commission then
          raise_application_error(SQLCODE, 'Error calculating bill. ' || SQLERRM, false);
      when error_updating_record then
          raise_application_error(SQLCODE, 'Error calculating bill. ' || SQLERRM, false);
      when others then
          raise_application_error(-20000, 'Unknown error encountered calculating bill.', false);
  end;
  
  create or replace procedure calculate_commission(in_num NUMBER) 
  is
  begin
      if in_num &lt; 30 then
          raise_application_error(-20001, 'Number too small to calculate commission.', false);
      elsif in_num &gt;= 30 and &lt; 40 then
          declare
              error_storing_record EXCEPTION;
              PRAGMA EXCEPTION_INIT (error_storing_record , -20002);
          begin
              update_record(in_num);
          exception
          when error_storing_record then
               raise_application_error(SQLCODE, 'Error calculating commission. ' || SQLERRM, false);
          when others then
               raise_application_error(-20001, 'Unknown error encountered calculating commission.', false);
      else
          raise_application_error(-20001, 'Number too large to calculate commission', false);
      end if;
  end;
  
  create or replace procedure update_record(in_num NUMBER) 
  is
  begin
    //some SQL query with a where clause, where in_num equals something
  exception
  when no_data_found then
      raise_application_error(-20002, 'No record exists to be updated', false);
  when others then
      raise_application_error(-20002, 'Unknown error encountered updating record.', false);
  end if;
  end;
  </code></pre>
  
  <p>Note: I know this example is a little contrived. I was just trying to keep it brief.</p>