PL /SQL - Exception handling

Exception Handling:

• The exception is an error condition during program execution.
• Exceptions are used to handle errors that occur in your PL/SQL code.

 Types of exception:

1. System defined.
2. User-defined.

Different parts of the exception

• Declare the exception
• Raise an exception
• Handle the exception

SYNTAX:
       EXCEPTION
            WHEN OTHERS THEN
            <statements>

• no_data_found: when a select into statement returns no rows.
• program_error: when pl/sql has an internal problem.
• storage_error: memory was corrupted.
• too_many_rows: when a select into statement returns more than a row.
• value_error:  when arithmetic, conversion, truncation, or size constraint error occurs.
• zero divide: when an attempt is made to divide a number by zero.
• invalid number.
• invalid cursor.

   This Exception Type of Error:

• ORA Core RDBMS errors
• PLS PL/SQL errors
• FRM Oracle Forms error
• REP Oracle Reports errors.

RAISE APPLICATION ERROR:

• Which is used to display the user-defined error messages along with the error number whose range is between -20000 and -20999.
• RAISE_APPLICATION_ERROR raises an exception but does not handle it.
• RAISE_APPLICATION_ERROR is used for the following reasons,

            a) to create a unique id for a user-defined exception.
            b) to make the user-defined exception look like an Oracle error.

Syntax:
       RAISE_APPLICATION_ERROR (error_number, error_message);

         • The Error number must be between -20000 and -20999
         • The Error_message is the message you want to display when the error occurs.

USER-DEFINED EXCEPTIONS:
     
        1. Declare exception
        2. RAISE exception
        3. Implement exception conditions.

Declare exception:

• must have to declare user define exception name in DECLARE block.

syntax:   
          user_define_exception_name EXCEPTION;

Raise exception:

• RAISE statement to raise defined exception name and control transfer to an EXCEPTION block.

syntax:
        RAISE user_define_exception_name;

Implement exception condition:

• In PL/SQL EXCEPTION block add the WHEN condition to implement a user define action.

syntax:
      WHEN user_define_exception_name THEN
User-defined statement (action) will be taken;


User-defined exception syntax:

DECLARE
user_define_exception_name EXCEPTION;
BEGIN
statement(s);
IF condition THEN
RAISE user_define_exception_name;
END IF;
EXCEPTION
WHEN user_define_exception_name THEN
User defined statement (action) will be taken;
END;

     
Purpose of sqlcode and sqlerrm:

• SQL code returns the numeric value for the error code.
• sqlerrm: returns the message associated with the error number.

           sqlcode                     description
            0                               no exception encountered.
            1                               user_defined exception.
            +100                         no_data_found exception.
           negative number    another oracle server error number.

            

DECLARE
           name employees.last_name%TYPE;
           v_code NUMBER;
           v_errm VARCHAR2(64);
BEGIN
       SELECT last_name INTO name FROM employees WHERE employee_id = 1000;
       EXCEPTION
           WHEN OTHERS THEN
             v_code := SQLCODE;
             v_errm := SUBSTR(SQLERRM, 1 , 64);
         Dbms_Output.Put_Line('The error code is ' || V_Code || '- ' || V_Errm);
END;