Better Oracle Exception Handling

While working with Oracle, I've learned more and more on better ways of handling exceptions.  There are some major specifics that need to be taken note of.  raise_application_error only works with custom error numbers.  You cannot use it to make Oracle throw just any exception.  Normally, you would use a predefined name like this: 

begin

      some select statement...

exception when no_data_found then

      --handle exception

end;

Sometimes, the Oracle error you want to handle is just not predefined.  So, you'd need a better way of handling errors and throwing some meaningful error messages back.

var b_err_code number;

begin

      some select statement...

exception when others then

      :b_err_code = SQLCODE;

      if :b_err_code = '-00904' then

          --handle exception

      else

          raise_application_error(-20000, 'Some Customized Error Message: ' || :b_err_code || ' Actual SQL Error: ' || SQLERRM);

      end if;

end;

Some points of interest here.  Using when others then allows you to handle all other cases.  Over here, we store the SQL error number to the bind variable b_err_code.  In Oracle, SQLCODE returns exactly that and in our example, we are expecting to handle ORA-00904 via the if condition.  We also have an else part where we generate our own application error.  We also use SQLERRM to return a human readable version of the SQL error message received, that would otherwise had gotten lost in the call stack.  Hope this little bit helps other Oracle and SQL developers.