PL/SQL Exceptions Tutorial 📝

Welcome back to CodeYourCraft! Today, we're diving into the fascinating world of PL/SQL Exceptions. If you're new to PL/SQL, don't worry! We'll cover the basics before diving into exceptions.

What is PL/SQL? 💡

PL/SQL stands for Procedural Language/Structured Query Language. It's an extended version of SQL used for building complex database applications in Oracle.

Understanding Exceptions in PL/SQL 🎯

Exceptions in PL/SQL are errors that occur during the execution of a PL/SQL block. They help us to handle and manage errors effectively.

Raising Exceptions 📝

We can raise exceptions using the RAISE keyword followed by the exception name.

sql

DECLARE
  my_exception EXCEPTION;
BEGIN
  RAISE my_exception;
EXCEPTION
  WHEN my_exception THEN
    DBMS_OUTPUT.PUT_LINE('An error occurred!');
END;

💡 Pro Tip: You can define your custom exceptions using the EXCEPTION keyword, like we did with my_exception.

Built-in Exceptions 📝

Oracle provides several built-in exceptions. Here are a few examples:

NO_DATA_FOUND: Raised when no data is found during a query.
DUplicate_VALUE_ON_NULL: Raised when an attempt is made to insert a duplicate value into a column that allows nulls.
ZLS_INVALID_NUMBER: Raised when an attempt is made to convert an invalid number.

Handling Exceptions 🎯

We can handle exceptions using the BEGIN...EXCEPTION block. The code within the BEGIN block is executed, and if an exception occurs, the code within the EXCEPTION block is executed.

sql

DECLARE
  my_exception EXCEPTION;
BEGIN
  RAISE my_exception;
EXCEPTION
  WHEN my_exception THEN
    DBMS_OUTPUT.PUT_LINE('An error occurred!');
END;

Exception Propagation 🎯

Exceptions can be propagated to the caller block. This means that if an exception occurs in a subprogram, it can be propagated to the main program.

sql

CREATE OR REPLACE PROCEDURE raise_exception AS
BEGIN
  RAISE my_exception;
END;

DECLARE
  my_exception EXCEPTION;
BEGIN
  raise_exception;
EXCEPTION
  WHEN my_exception THEN
    DBMS_OUTPUT.PUT_LINE('An error occurred!');
END;

Quiz Time 🎯

That's it for today! In the next lesson, we'll dive deeper into handling and managing exceptions in PL/SQL. Until then, happy coding! 💪