Exception handling in PLSQL | Predefined and User Defined

In PL/SQL, exception handling is used to handle runtime errors so that normal execution can continue. Exceptions in PL/SQL can be predefined or user-defined. Here's an explanation of both types of exceptions, along with examples of how to handle them:

---

1. Predefined Exceptions

These are exceptions that PL/SQL already defines for common Oracle errors. You don't need to declare them explicitly.

Example of Predefined Exception:

BEGIN
-- Attempt to divide by zero, which raises a predefined exception.
DECLARE
num1 NUMBER := 10;
num2 NUMBER := 0;
result NUMBER;
BEGIN
result := num1 / num2; -- Raises ZERO_DIVIDE exception
EXCEPTION
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE('Cannot divide by zero');
END;
END;
/

Predefined Exceptions: PL/SQL provides many predefined exceptions like:

NO_DATA_FOUND

ZERO_DIVIDE

TOO_MANY_ROWS

INVALID_NUMBER

---

1. User-Defined Exceptions

You can define your own exceptions to handle specific conditions in your application. There are three main types of user-defined exceptions in PL/SQL:

A. Basic User-Defined Exception (Using EXCEPTION keyword)

You declare an exception using the EXCEPTION keyword and raise it using the RAISE statement.

Example of Basic User-Defined Exception:

DECLARE
insufficient_funds EXCEPTION; -- User-defined exception
account_balance NUMBER := 1000;
withdraw_amount NUMBER := 1500;
BEGIN
IF withdraw_amount > account_balance THEN
RAISE insufficient_funds; -- Raising the user-defined exception
END IF;
EXCEPTION
WHEN insufficient_funds THEN
DBMS_OUTPUT.PUT_LINE('Error: Insufficient funds');
END;
/

B. Using RAISE_APPLICATION_ERROR

RAISE_APPLICATION_ERROR is used to raise an application-specific error with a custom error code and message.

Example of RAISE_APPLICATION_ERROR:

DECLARE
account_balance NUMBER := 500;
withdraw_amount NUMBER := 1000;
BEGIN
IF withdraw_amount > account_balance THEN
RAISE_APPLICATION_ERROR(-20001, 'Withdrawal amount exceeds account balance');
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM); -- Prints the error message
END;
/

Explanation: The RAISE_APPLICATION_ERROR is used to generate a custom error with an error number (in the range -20000 to -20999) and a message.

C. Using PRAGMA EXCEPTION_INIT

You can associate an Oracle error number with a user-defined exception using PRAGMA EXCEPTION_INIT.

Example of PRAGMA EXCEPTION_INIT:

DECLARE
insufficient_balance EXCEPTION;
PRAGMA EXCEPTION_INIT(insufficient_balance, -20001); -- Associate a number with the exception
BEGIN
RAISE_APPLICATION_ERROR(-20001, 'Custom error: insufficient balance');
EXCEPTION
WHEN insufficient_balance THEN
DBMS_OUTPUT.PUT_LINE('Caught the insufficient balance error');
END;
/

---

Summary of User-Defined Exception Types:

1. Basic Exception: Declared using the EXCEPTION keyword, raised using RAISE.

2. Application Error: Raised using RAISE_APPLICATION_ERROR with a custom error code and message.

3. PRAGMA EXCEPTION_INIT: Links an Oracle error number to a user-defined exception.

---

Key Points:

Predefined exceptions are built-in, and you don’t need to declare them.

User-defined exceptions can be declared and raised based on specific business logic.

RAISE_APPLICATION_ERROR allows you to raise custom exceptions with error numbers.

PRAGMA EXCEPTION_INIT allows you to associate a standard Oracle error with a user-defined exception.

This structure helps handle both predefined and custom exceptions efficiently in PL/SQL.