The EXCEPTION Section Handles Errors. There are three types of Exceptions:

- Predefined Oracle Server Error: Do not declare, let server raise implicitly.

- Non-Predefined Oracle Server Error: Declare within DECLARE section, but let server raise implicitly.

- User Defined Error: Declare within DECLARE section, raise explicitly.

Syntax

EXCEPTION

WHEN exception1 [OR exception2�] THEN

Statement1;

Statement2;

[WHEN exception3 THEN

statement1;

statement2;]

[WHEN OTHERS THEN

statement1;

statement2;]

Note the indentation above, failure to indent for readability will result in one full letter grade deduction and your work being returned to you to fix. Also I will not help you with any program that is NOT readable.

Common Pre-Defined Exceptions:

- NO_DATA_FOUND: Single row SELECT returned no data.

- TOO_MANY_ROWS: Single row SELECT returned more than 1 row.

- INVALID_CURSOR: Illegal Cursor operation occurred.

- VALUE_ERROR: Arithmetic, conversion, truncation, or constraint error.

- INVALID_NUMBER: Conversion of a character string to a number failed.

- ZERO_DEVIDE: Attempted to dived by zero.

- DUP_VAL_ON_INDEX: Attempted to insert a duplicate value in a column with a unique index

Handling Non-predefined Exceptions:

- Declare a name for the exception in the DECLARE section
E_except_var EXCEPTION;

- Associate it with an Oracle error in the DECLARE section.
PRAGMA EXCEPTION_INIT(e_except_var, err_num);

- Reference the declared exception in the EXCEPTION section with the appropriate code to handle it

Handling User Defined Exceptions:

- Declare a name for the exception in the DECLARE section.

E_exception_var EXCEPTION;

- Raise the exception explicitly in the BEGIN section.
RAISE e_except_var;

- Reference the declared exception in the EXCEPTION section with the appropriate code to handle it.

Handling Other Exceptions:

- Declare a variable for code and message in the DECLARE section.

V_error_code NUMBER;

V_error_message VARCHAR2(255);

- Assign these variables the SQLCODE and SQLERRM functions and write them to an exception table.

V_error_code := SQLCODE;

V_error_message := SQLERRM;

NOTE: SQLCODE > 0 is a recoverable Error, SQLCODE < 0 is NOT recoverable.

Pre-Defined Error Handling Example:

SET SERVEROUTPUT ON;
ACCEPT p_customer_nm PROMPT 'Enter Customer Name';

DECLARE
V_lname customer.lname%TYPE := '&p_customer_nm';
V_fname customer.fname%TYPE;
BEGIN

          SELECT fname INTO v_fname
          FROM customer
          WHERE lname = v_lname;

DBMS_OUTPUT.PUT_LINE(v_fname);

COMMIT WORK;

EXCEPTION

          WHEN NO_DATA_FOUND THEN
                    ROLLBACK WORK;
                    DBMS_OUTPUT.PUT_LINE ('No Data Found');

          WHEN TOO_MANY_ROWS THEN
                    ROLLBACK WORK;
                    DBMS_OUTPUT.PUT_LINE ('Too Many Rows Found');

          WHEN OTHERS THEN
                    ROLLBACK WORK ;
                    DBMS_OUTPUT.PUT_LINE (SQLERRM);

END;

Non-Predefined Error Handling Example:

DECLARE
    v_fk EXCEPTION;
    PRAGMA EXCEPTION_INIT(v_fk, -2291);
BEGIN
    INSERT INTO orders VALUES (7,SYSDATE,7);
EXCEPTION
    WHEN v_fk THEN
        dbms_output.put_line ('FK Violated');
    WHEN OTHERS THEN
        dbms_output.put_line (SQLCODE || ' = ' || SQLERRM);
END;
/

ACCEPT p_customer_nr PROMPT 'Enter Customer #';

DECLARE
V_customer_nr customer.customer_number%TYPE := &p_customer_nr;
V_last customer.lname%TYPE;

V_error_code NUMBER;

V_error_message VARCHAR2(255);

E_customer_error EXCEPTION;

PRAGMA EXCEPTION_INIT (e_customer_error, 100); -- NO_DATA_FOUND.

BEGIN

          SELECT lname INTO v_last
          FROM customer
          WHERE customer_number = v_customer_nr;

COMMIT WORK;

EXCEPTION
          WHEN e_customer_error THEN
                    INSERT INTO errorlog (error_dt, user_id, error_code, error_msg)
                             VALUES (SYSDATE, USER, 0, 'Customer Error');

          WHEN OTHERS THEN
                    ROLLBACK WORK;
                         V_error_code := SQLCODE;
                        V_error_message := SQLERRM;
                    INSERT INTO errorlog (error_dt, user_id, error_code, error_msg)
                             VALUES (SYSDATE, USER, v_error_code, v_error_message);

COMMIT;

END;
/

User-Defined Error Handling Example:

ACCEPT p_customer_nr PROMPT 'Enter Customer #';

DECLARE
V_customer_nr customer.customer_number%TYPE := &p_customer_nr;
V_balance customer.balance%TYPE;

V_error_code NUMBER;

V_error_message VARCHAR2(255);

E_customer_error EXCEPTION;

BEGIN

          SELECT balance INTO v_balance
          FROM customer
          WHERE customer_number = v_customer_nr;

          IF v_balance < 0 THEN
                    RAISE e_customer_error;
          END IF;
          DBMS_OUTPUT.PUT_LINE ('Customer Balance is: ' || v_balance);

COMMIT WORK;

EXCEPTION
        WHEN e_customer_error THEN
                    INSERT INTO errorlog (error_dt, user_id, error_code, error_msg)
                             VALUES (SYSDATE, USER, 0, 'Negative Balance');
        WHEN OTHERS THEN
                    ROLLBACK WORK ;
                    v_error_code := SQLCODE;
                    v_error_message := SQLERRM;
                   INSERT INTO errorlog (error_dt, user_id, error_code, error_msg)
                             VALUES (SYSDATE, USER, v_error_code, v_error_message);
                    COMMIT;

END;
/

Nested Blocks Localize Exceptions:

ACCEPT p_customer_nr PROMPT 'Enter Customer #';

DECLARE
V_customer_nr customer.customer_number%TYPE := &p_customer_nr;
V_last customer.lname%TYPE;

BEGIN

          SELECT lname INTO v_last
                    FROM customer
                    WHERE customer_number = v_customer_nr;

COMMIT WORK;

END;

dbms_output.put_line ('Middle Block Here...');

EXCEPTION

WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE ('Middle Block: Error No Data Found');

-- Insert Into Customer ...

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE ('Error Selecting Customer Data');

RAISE_APPLICATION_ERROR (-10001, 'Error Message to Client Here');

END;

dbms_output.put_line ('Outer Block Here...');

-- UPDATE customer ...

EXCEPTION

WHEN NO_DATA_FOUND THEN ROLLBACK WORK;

WHEN TOO_MANY_ROWS THEN ROLLBACK WORK;

WHEN OTHERS THEN ROLLBACK WORK;

END;
/