Oracle PLSQL Simplified: Exceptions

1. What is an Exception?

An exception is an error condition during the execution of a PL/SQL block. When an exception occurs, PL/SQL transfers control to the exception-handling part of the block.

2. Types of Exceptions

A. Predefined Exceptions

Oracle provides predefined exceptions for common errors:

NO_DATA_FOUND – Raised when a SELECT INTO returns no rows.
TOO_MANY_ROWS – Raised when a SELECT INTO returns more than one row.
ZERO_DIVIDE – Raised when attempting to divide by zero.
VALUE_ERROR – Raised for numeric or conversion errors.
INVALID_CURSOR – Raised when performing an illegal cursor operation.

B. User-Defined Exceptions

Declared by the programmer using the EXCEPTION keyword.
Raised manually with the RAISE statement.

C. Non-Predefined Oracle Errors

Some Oracle errors do not have predefined exceptions.
You can use PRAGMA EXCEPTION_INIT to associate a specific Oracle error number with a named exception.

3. Exception Handling Structure

A PL/SQL block with exception handling has the structure:


DECLARE
   -- Declarations (variables, exceptions, etc.)
BEGIN
   -- Executable statements
EXCEPTION
   WHEN <exception_name> THEN
       -- Actions to take when exception occurs
   WHEN OTHERS THEN
       -- Actions for all unhandled exceptions
END;

4. Basic Example – Handling a Predefined Exception

DECLARE
   v_num1 NUMBER := 10;
   v_num2 NUMBER := 0;
   v_result NUMBER;
BEGIN
   v_result := v_num1 / v_num2;   -- Division by zero error
   DBMS_OUTPUT.PUT_LINE('Result: ' || v_result);

EXCEPTION
   WHEN ZERO_DIVIDE THEN
      DBMS_OUTPUT.PUT_LINE('Error: Division by zero is not allowed.');
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Unexpected error: ' || SQLERRM);
END;
/

Output:


Error: Division by zero is not allowed.

5. Example – Handling NO_DATA_FOUND


DECLARE
   v_emp_name VARCHAR2(50);
BEGIN
   SELECT emp_name
   INTO v_emp_name
   FROM emp
   WHERE emp_id = 999;   -- No record exists

   DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_emp_name);

EXCEPTION
   WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('Error: No employee found with that ID.');
END;
/

6. User-Defined Exception Example


DECLARE
   e_invalid_salary EXCEPTION;  -- Declare exception
   v_salary NUMBER := -1000;
BEGIN
   IF v_salary < 0 THEN
      RAISE e_invalid_salary;   -- Raise user-defined exception
   END IF;

   DBMS_OUTPUT.PUT_LINE('Salary: ' || v_salary);

EXCEPTION
   WHEN e_invalid_salary THEN
      DBMS_OUTPUT.PUT_LINE('Error: Salary cannot be negative.');
END;
/

Output:


Error: Salary cannot be negative.

7. Handling All Exceptions – WHEN OTHERS

WHEN OTHERS catches all exceptions that are not explicitly handled:


EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);  -- SQLERRM returns the error message

8. Key Points

Order matters – The first matching exception handler is executed.
Avoid using only WHEN OTHERS as it can mask real issues. Always log SQLCODE and SQLERRM.
Use RAISE to propagate exceptions to the caller if needed.

9. Comprehensive Example


DECLARE
   v_num NUMBER := 0;
   v_res NUMBER;
   e_custom EXCEPTION;
BEGIN
   -- Division by zero
   v_res := 10 / v_num;

   -- Raise custom exception
   IF v_res < 0 THEN
      RAISE e_custom;
   END IF;

EXCEPTION
   WHEN ZERO_DIVIDE THEN
      DBMS_OUTPUT.PUT_LINE('Caught ZERO_DIVIDE: Division by zero');
   WHEN e_custom THEN
      DBMS_OUTPUT.PUT_LINE('Caught User-Defined Exception');
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Caught Other Exception: ' || SQLERRM);
END;
/


10. Comprehensive Example With Exception Log-

--============================================================
-- Cleanup (ignore errors if tables do not exist)
--============================================================
BEGIN EXECUTE IMMEDIATE 'DROP TABLE emp_error_log PURGE'; EXCEPTION WHEN OTHERS THEN NULL; END;
/
BEGIN EXECUTE IMMEDIATE 'DROP TABLE employee_dst PURGE'; EXCEPTION WHEN OTHERS THEN NULL; END;
/
BEGIN EXECUTE IMMEDIATE 'DROP TABLE employee_src PURGE'; EXCEPTION WHEN OTHERS THEN NULL; END;
/
BEGIN EXECUTE IMMEDIATE 'DROP SEQUENCE emp_error_log_seq'; EXCEPTION WHEN OTHERS THEN NULL; END;
/
--============================================================
-- 1. Source table: NO constraints
--============================================================
CREATE TABLE employee_src (
    emp_id    NUMBER ,
    emp_name  VARCHAR2(50),
    salary    NUMBER,
    dept_id   NUMBER
);

-- Sample source data (some bad rows intentionally)
INSERT INTO employee_src VALUES (100, 'Amit',     50000, 10);
INSERT INTO employee_src VALUES (101, 'Neha',     60000, 20);
INSERT INTO employee_src VALUES (102, NULL,       45000, 10);  -- NULL name
INSERT INTO employee_src VALUES (103, 'Raj',          -1, 10); -- negative salary
INSERT INTO employee_src VALUES (101, 'Neha-dup', 62000, 30);  -- duplicate PK
INSERT INTO employee_src VALUES (104, 'Sunil',    70000, 30);
INSERT INTO employee_src VALUES (105, 'Pooja',    55000, 20);
COMMIT;

--============================================================
-- 2. Destination table: WITH constraints
--============================================================
CREATE TABLE employee_dst (
    emp_id    NUMBER       CONSTRAINT employee_dst_pk PRIMARY KEY,
    emp_name  VARCHAR2(50) CONSTRAINT employee_dst_nn_name NOT NULL,
    salary    NUMBER       CONSTRAINT employee_dst_ck_sal CHECK (salary >= 0),
    dept_id   NUMBER
);

--============================================================
-- 3. Error Log Table
--============================================================

CREATE TABLE emp_error_log (
    log_id     NUMBER ,
    emp_id     NUMBER,
    emp_name   VARCHAR2(50),
    salary     NUMBER,
    dept_id    NUMBER,
    error_code NUMBER,
    error_msg  VARCHAR2(4000)
);

CREATE SEQUENCE emp_error_log_seq START WITH 1 INCREMENT BY 1;

--============================================================
-- 4. PL/SQL block: insert rows, log failed records
--============================================================
SET SERVEROUTPUT ON;

DECLARE
    CURSOR c_src IS
        SELECT emp_id, emp_name, salary, dept_id
        FROM employee_src
        ORDER BY emp_id;

    v_fail_count   PLS_INTEGER := 0;
    v_success_cnt  PLS_INTEGER := 0;

    v_err_code     NUMBER;
    v_err_msg      VARCHAR2(4000);
BEGIN
    FOR r IN c_src LOOP
        BEGIN
            INSERT INTO employee_dst (emp_id, emp_name, salary, dept_id)
            VALUES ( r.emp_id, r.emp_name, r.salary, r.dept_id);

            v_success_cnt := v_success_cnt + 1;

        EXCEPTION
--            WHEN OTHERS THEN NULL;
            WHEN OTHERS THEN 
                v_fail_count := v_fail_count + 1;

                -- Capture error details into PL/SQL vars
                v_err_code := SQLCODE;
                v_err_msg  := SQLERRM;

                -- Log the failed row
                INSERT INTO emp_error_log
                    (log_id,emp_id,emp_name, salary, dept_id, error_code, error_msg)
                VALUES
                    (emp_error_log_seq.NEXTVAL,r.emp_id, r.emp_name, r.salary, r.dept_id,
                     v_err_code, SUBSTR(v_err_msg,1,4000));
        END;
    END LOOP;

    COMMIT;

    DBMS_OUTPUT.PUT_LINE('--------------------------------------');
    DBMS_OUTPUT.PUT_LINE('Rows inserted successfully : '||v_success_cnt);
    DBMS_OUTPUT.PUT_LINE('Rows failed (logged)       : '||v_fail_count);
    DBMS_OUTPUT.PUT_LINE('--------------------------------------');
END;
/
                            Source Table :

                            Destination Table :
Exception Log Table:

Oracle PLSQL Simplified

Exceptions