Exception Handling in PL/SQL

Exception handling in PL/SQL allows developers to gracefully manage errors that occur during program execution. This mechanism improves program robustness, making it easier to isolate, diagnose, and recover from unexpected conditions.

What is an Exception?

In PL/SQL, an exception is an error condition that changes the normal flow of execution. Exceptions can be:

Predefined: Automatically raised by Oracle (e.g., NO_DATA_FOUND, ZERO_DIVIDE).
User-defined: Declared and raised explicitly by the programmer.

PL/SQL exceptions are somewhat similar to Java exceptions, but unlike Java, PL/SQL exceptions are not objects.

Types of Errors in PL/SQL

Error Type	Reported By	How Handled
Compile-time	PL/SQL Compiler	Detected before execution
Run-time	PL/SQL Run-time Engine	Managed via exception handlers

Example of Compile-Time Error

DECLARE
  v_NumAuthors NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_NumAuthors FROM aauthors; -- Typo: 'aauthors'
END;

Error: ORA-00942: table or view does not exist

Declaring Exceptions

User-Defined Exceptions

DECLARE
  e_DuplicateAuthors EXCEPTION;

Predefined Exceptions

Oracle provides several built-in exceptions through the STANDARD package.

Oracle Error	Exception Name	Description
ORA-00001	DUP_VAL_ON_INDEX	Unique constraint violated
ORA-01403	NO_DATA_FOUND	SELECT INTO returns no rows
ORA-01476	ZERO_DIVIDE	Division by zero
ORA-06502	VALUE_ERROR	Numeric or value error

Raising Exceptions

User-Defined Example

DECLARE
  e_DuplicateAuthors EXCEPTION;
  v_Author1 books.author1%TYPE;
  v_Author2 books.author2%TYPE;
  v_Author3 books.author3%TYPE;
BEGIN
  SELECT author1, author2, author3
  INTO v_Author1, v_Author2, v_Author3
  FROM books
  WHERE title = 'Oracle9i DBA 101';

  IF (v_Author1 = v_Author2) OR (v_Author1 = v_Author3) OR
     (v_Author2 = v_Author3) THEN
    RAISE e_DuplicateAuthors;
  END IF;
END;

Handling Exceptions

Basic Syntax

EXCEPTION
  WHEN exception_name THEN
    -- handling code
  WHEN OTHERS THEN
    -- generic error handler
END;

Example

EXCEPTION
  WHEN e_DuplicateAuthors THEN
    INSERT INTO log_table (info)
    VALUES ('Duplicate authors found');
  WHEN OTHERS THEN
    INSERT INTO log_table (info)
    VALUES ('An unknown error occurred');
END;

Multiple Exceptions in One Handler

EXCEPTION
  WHEN NO_DATA_FOUND OR TOO_MANY_ROWS THEN
    INSERT INTO log_table (info)
    VALUES ('A select error occurred');
END;

The OTHERS Handler

This is a catch-all handler for unanticipated exceptions. It must be last in the list.

WHEN OTHERS THEN
  -- Log error or take corrective action
END;

Bad Practice:

WHEN OTHERS THEN NULL;

Getting Error Details

SQLCODE and SQLERRM

SQLCODE: Returns the numeric error code
SQLERRM: Returns the full error message text

Example

DECLARE
  v_ErrorCode NUMBER;
  v_ErrorText VARCHAR2(200);
BEGIN
  -- executable code
EXCEPTION
  WHEN OTHERS THEN
    v_ErrorCode := SQLCODE;
    v_ErrorText := SUBSTR(SQLERRM, 1, 200);
    INSERT INTO log_table (code, message)
    VALUES (v_ErrorCode, v_ErrorText);
END;

Behavior

Function	Returned Value
`SQLCODE`	Error number (e.g., -1, -54, 100)
`SQLERRM(100)`	`ORA-01403: no data found`
`SQLERRM(0)`	`ORA-0000: normal, successful completion`

Summary

Use user-defined exceptions to handle custom business logic.
Always include an OTHERS handler at the top level.
Use SQLCODE, SQLERRM, FORMAT_ERROR_STACK, and FORMAT_ERROR_BACKTRACE for effective debugging.
Avoid silent handlers like WHEN OTHERS THEN NULL;

AmantPoint Exclusive Learning Series
© 2025 AmantPoint. All rights reserved. Educational content for developers and learners.