PL/SQL - Exceptions: A Beginner's Guide

Hello there, future PL/SQL maestros! Today, we're going to dive into the fascinating world of exceptions in PL/SQL. Don't worry if you're new to programming – I'll guide you through this topic step by step, just like I've done for countless students over my years of teaching. So, grab a cup of coffee (or tea, if that's your thing), and let's embark on this exciting journey together!

PL/SQL - Exceptions

What Are Exceptions?

Before we jump into the nitty-gritty, let's understand what exceptions are. Imagine you're baking a cake (stick with me here, I promise this relates to programming). You follow the recipe step by step, but suddenly, you realize you're out of eggs! This unexpected situation is similar to an exception in programming.

In PL/SQL, exceptions are unexpected events that disrupt the normal flow of your program. They can be errors like trying to divide by zero or attempting to insert a duplicate value into a unique column. Instead of letting these issues crash our program, we can "handle" them gracefully – just like how you might use a substitute for eggs in our baking analogy.

Syntax for Exception Handling

Now, let's look at how we actually handle these exceptions in PL/SQL. The basic structure looks like this:

BEGIN
   -- Your normal code goes here
EXCEPTION
   WHEN exception_name1 THEN
      -- Handle exception 1
   WHEN exception_name2 THEN
      -- Handle exception 2
   WHEN OTHERS THEN
      -- Handle all other exceptions
END;

Let's break this down:

We write our normal code in the BEGIN block.
If an exception occurs, the program jumps to the EXCEPTION block.
We can handle specific exceptions using WHEN clauses.
The WHEN OTHERS clause catches any exceptions we haven't specifically handled.

Here's a simple example:

DECLARE
   v_result NUMBER;
BEGIN
   v_result := 10 / 0;  -- This will cause a divide-by-zero error
   DBMS_OUTPUT.PUT_LINE('Result: ' || v_result);
EXCEPTION
   WHEN ZERO_DIVIDE THEN
      DBMS_OUTPUT.PUT_LINE('Error: Cannot divide by zero!');
END;

In this example, we're trying to divide 10 by 0, which is mathematically impossible. Instead of crashing, our program catches the ZERO_DIVIDE exception and prints a friendly error message.

Raising Exceptions

Sometimes, we want to create our own exceptions when certain conditions are met. We can do this using the RAISE statement. It's like being the referee in a soccer game – you see a foul, you blow the whistle!

Here's how it works:

DECLARE
   v_age NUMBER := 15;
BEGIN
   IF v_age < 18 THEN
      RAISE_APPLICATION_ERROR(-20001, 'Must be 18 or older');
   END IF;
   DBMS_OUTPUT.PUT_LINE('Welcome to the club!');
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;

In this example, we're checking if someone is old enough to enter a club. If they're under 18, we raise an exception with a custom error message. The WHEN OTHERS clause catches this exception and prints the error message.

User-defined Exceptions

While PL/SQL provides many pre-defined exceptions, sometimes we need to create our own. It's like inventing a new rule in our game. Here's how we can do it:

DECLARE
   e_invalid_name EXCEPTION;
   v_name VARCHAR2(50) := 'J0hn';
BEGIN
   IF NOT REGEXP_LIKE(v_name, '^[A-Za-z]+$') THEN
      RAISE e_invalid_name;
   END IF;
   DBMS_OUTPUT.PUT_LINE('Name is valid: ' || v_name);
EXCEPTION
   WHEN e_invalid_name THEN
      DBMS_OUTPUT.PUT_LINE('Error: Name should only contain letters');
END;

In this example, we've created a custom exception e_invalid_name. We raise this exception if the name contains anything other than letters. This allows us to handle this specific scenario in a way that makes sense for our program.

Pre-defined Exceptions

PL/SQL comes with a set of pre-defined exceptions for common error scenarios. It's like having a first-aid kit ready for common injuries. Here are some of the most frequently used ones:

Exception Name	Description
NO_DATA_FOUND	Raised when a SELECT INTO statement returns no rows
TOO_MANY_ROWS	Raised when a SELECT INTO statement returns more than one row
ZERO_DIVIDE	Raised when attempting to divide by zero
DUP_VAL_ON_INDEX	Raised when attempting to insert a duplicate value in a unique index
VALUE_ERROR	Raised when there's an arithmetic, conversion, truncation, or size constraint error

Let's see an example using a pre-defined exception:

DECLARE
   v_emp_name VARCHAR2(50);
BEGIN
   SELECT first_name INTO v_emp_name
   FROM employees
   WHERE employee_id = 1000;  -- Assuming this ID doesn't exist

   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');
   WHEN TOO_MANY_ROWS THEN
      DBMS_OUTPUT.PUT_LINE('Error: More than one employee found');
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('An unexpected error occurred: ' || SQLERRM);
END;

In this example, we're trying to fetch an employee's name. If no employee is found, we catch the NO_DATA_FOUND exception. If somehow multiple employees are found, we catch the TOO_MANY_ROWS exception. Any other unexpected errors are caught by the WHEN OTHERS clause.

And there you have it, folks! We've covered the basics of exception handling in PL/SQL. Remember, handling exceptions is like wearing a seatbelt – it might seem unnecessary when everything's going smoothly, but it can save you a lot of trouble when things go wrong.

Practice these concepts, play around with different scenarios, and soon you'll be handling exceptions like a pro. Happy coding, and may your programs always gracefully handle the unexpected!

Credits: Image by storyset