Can you tell me about Exceptions?

Exception means an Error.
In PL/SQL error are called exceptions.
When an exception is raised, processing jumps to the exception handlers.
An exception handler is a sequence of statements to be processed when a certain exception occurs.
When an exception handler is complete processing of the block terminates.
Exception Is a mechanism provided by pl/sql to detect run time errors and process them with out halting the program abnormally.

1)   PRE DEFINED
2)   USER DEFINED
3) Non Pre-defined Exceptions

PRE-DEFINED:

 Defined by oracle
Activated by oracle automatically
Solution provided by User

 1)cursor_already_open ------ attempted to open an already open cursor.
 2)dup_val_on_index     ------ attempted to insert a duplicate values.
 3)Invalid_cursor           ------ illegal cursor operation occurred.
 4)Invalid_number         ------ conversion of character string to number fails.
 5)Login_denied            ------ loging on to oracle with an invalid user name and password.
 6)program_error           ------ pl/sql has an internal problem. 
 7)storage_error             ------ pl/sql ran out of memory or memory is corrupted.
 8)to_many_row            ------ single row select returned more than one row.
 9)value_error               ------ arithmetic,conversion,truncation or size constraint error
10)zero_devided           ------ attempted to divided by zero.

USER-DEFINED:
Defined by user
Activated by user using "Raise" Stmt
solution provided by user.

       Declare      : name the exception

       Raise          : explicitly raise the exception by using the raise statements
       Reference : exception handing section.

Non Pre-defined Exceptions 

     (Undefined Exceptions)
    - Defined by user
    - Activated automatically by constraints
    - Solution provided by user
    - used to handle constraint voilation errors in                pl/sql block. [ pragma exception_init ]

There are four kinds of exceptions in PL/SQL:

1. Named system exceptions: Exceptions that have been given names by PL/SQL and raised as a result of an error in PL/SQL or RDBMS processing.

For example: NO_DATA_FOUND and ZERO_DIVIDE are called Named System exceptions.

2. Named programmer-defined exceptions:Exceptions that are raised as a result of errors in your application code. You give these exceptions names by declaring them in the declaration section. You then raise the exceptions explicitly in the program.

EX : Exception_name EXCEPTION;

3. Unnamed system exceptions: Exceptions that are raised as a result of an error in PL/SQL or RDBMS processing but have not been given names by PL/SQL. Only the most common errors are so named; the rest have numbers and can be assigned names with the special PRAGMA EXCEPTION_INIT syntax.

EX : Child_rec_exception EXCEPTION;
        PRAGMA EXCEPTION_INIT (Child_rec_exception, -2292);

4. Unnamed programmer-defined exceptions: Exceptions that are defined and raised in the server by the programmer. In this case, the programmer provides both an error number (between -20000 and -20999) and an error message, and raises that exception with a call to RAISE_APPLICATION_ERROR. That error, along with its message, is propagated back to the client-side application.


EX : RAISE_APPLICATION_ERROR (error_number, error_message);

The Raise_Application_Error_Procedure:

You can use this procedure to issue user-defined error messages from stored sub programs.

You can report errors to your applications and avoid returning unhandled exceptions.

Syntax:
Raise_Application_Error(error_number,message[,{true/false}]
                 Error number è between -20000 to -20999

pragma exception_init?

It tells the compiler to associate an exception with an oracle error. To get an error message of a specific oracle error.


Ex: pragma exception_init(exception name, oracle error number)


Examples:

Check the record is exist or not?

              Declare
                     E  emp% rowtype
             Begin
                     e.empno := &empno;
                     select * into e from emp where empno =e.empno;
                     Dbms_output.putline(‘empno’ || e.empno);
            Exception
                    When no_data_found then
                    Dbms_output.putline(e.empno ||’doest exist’);

            End;

User defined exceptions?

                   Define p_dept_desc =’Oracleapps4u’
                   Define p_dept_number =1236
   Declare
                           E_invalid_dept exception;
                    Begin
                  Update departments Set dept_name=’&p_dept_desc’ Where dept_id =’&p_dept_number’;
                        If sql% not found then
                            Raise e_invalid_departments;
                        End if;
                        Commit;
                    Exception
                           When e_invalid_departments then
                           Dbms_output.putline(‘no such dept’);
                    End;