Plsql Exception Management - Part 1

Achieving ideal error management
You should take care of the following points
1-Define your requirements clearly
2-Understand PL/SQL error management features and make full use of what PL/SQL has to offer
3-When will errors be raised, when handled?
     Do you let errors go unhandled to the host, trap locally, or trap at the top-most level?
4-How should errors be raised and handled?
    Will users do whatever they want or will there be standard approaches that everyone will follow?
5-Useful to conceptualize errors into three categories: Deliberate, unfortunate, unexpected

PL/SQL error management features
1- Defining exceptions
2- Raising exceptions
3- Handing exceptions

We will explain every topic individually

1-Defining exceptions

The EXCEPTION is a limited type of data.
Has just two attributes: code and message.
You can RAISE and handle an exception, but it cannot be passed as an argument in a program.
syntax

pragma exception_init(error_name,-error_number);

example :-

declare

   bulk_erros exception

   pragma exception_init(bulk_erros,-20006);

begin

  ...

end;

2-Raising exceptions 
a-RAISE raises the specified exception by name.
   RAISE; re-raises current exception. Callable only within the exception section. b-RAISE_APPLICATION_ERROR
  Communicates an application specific error back to a non-PL/SQL host environment.
  Error numbers restricted to the -20,999 - -20,000 range.
  Syntax :-

  RAISE_APPLICATION_ERROR (num binary_integer, msg varchar2, 

                                                                       keeperrorstack boolean default FALSE);

  Example :
  RAISE_APPLICATION_ERROR (-20070, ‘Employee must be more than 18 years old.’);

3-Handing exceptions  

a-The EXCEPTION section consolidates all error handling logic in a block.
b-We can use the following functions at exception section:
   –SQLCODE --> get error number.
   –SQLERRM--> get error message.
   –DBMS_UTILITY.FORMAT_ERROR_STACK
       Returns exception in more details.
   –DBMS_UTILITY.FORMAT_ERROR_BACKTRACE
      Returns the full stack of errors with line number information.number that raised the error.
      This stack is available only if you the error is unhandled.
c-The DBMS_ERRLOG package
   –Quick and easy logging of DML errors
d- We can use The AFTER SERVERERROR trigger to log any error have been raised at database.


Advice
Every developer should develop error package to use for exception management and he will have a lot feature
a-single procedure to raise exception.
b-single function to get error meaning from his error table.
c-He can logging exception at separate database table.
d-Single point of maintenance.

I am developing generic exception package now, I will publish it at part 2 ISA.

Thanks
Mahmoud A. El-Sayed