Exception Handling in PL/SQL

📖 Theory

When code crashes silently

Imagine you write a PL/SQL tool for TicketDesk to look up a ticket by its ID number. If the user types an ID that exists, everything works perfectly. But what happens if they type an ID that does not exist? Oracle instantly throws a runtime error and halts your entire application on the spot. Your beautiful user interface freezes, and your database connection snaps. How can you protect your software from collapsing when unpredictable data errors occur during live execution?

📖 Theory

The Electrical Circuit Fuse

Think of runtime errors like sudden power surges in a building. If a massive spike of electricity hits an unprotected appliance, it burns the wires and destroys the machine completely. To prevent this, engineers install a small fuse. When a surge happens, the fuse safely breaks the connection, intercepts the danger, and keeps the house safe in the dark. Exception handling is the electrical fuse for your database code, catching crashes before they break the system.

📖 Theory

Defining Exception Handling

In the Oracle database dialect, an exception is a runtime error triggered during block execution. Exception handling is the specialized procedural section placed at the bottom of a PL/SQL block designed to catch and process these errors gracefully. Oracle categorizes these into predefined exceptions, which are built-in errors automatically named by the system like NO_DATA_FOUND or TOO_MANY_ROWS, and user-defined exceptions, which are custom errors declared manually by programmers for specific business rules.

📊 At a glance

Common pre-defined runtime exceptions in Oracle PL/SQL systems

⌨️ Practical

Safely Fetching Ticket Details

-- Enable terminal output display
SET SERVEROUTPUT ON;

DECLARE
  v_title tickets.title%TYPE;
BEGIN
  -- This query will fail if ID does not exist or matches multiple rows
  SELECT title INTO v_title FROM tickets WHERE id = 999;
  
  DBMS_OUTPUT.PUT_LINE('Ticket Title: ' || v_title);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Error: That ticket ID was not found in TicketDesk.');
    
  WHEN TOO_MANY_ROWS THEN
    DBMS_OUTPUT.PUT_LINE('Error: Multiple tickets found with that same ID.');
    
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('An unexpected error occurred in the system.');
END;
/

🧩 Quiz

If a SELECT INTO statement returns zero rows inside a PL/SQL block that lacks an EXCEPTION section, what happens to the statements located right after that query?

1. They execute normally because Oracle ignores the missing row.
2. They are completely skipped because the entire block terminates instantly.
3. They execute but all local variables are set to NULL.
4. They loop indefinitely until the database connection times out.

💭 Think first

The Scope of OTHERS

If you place the WHEN OTHERS handler at the very top of your EXCEPTION block before WHEN NO_DATA_FOUND, how will the Oracle compiler react? Work out the logic mentally before checking.

⚠️ Watch out

The Blind Catch-All Black Hole

The most frequent error Indian BCA students make in university laboratory practicals is using WHEN OTHERS THEN NULL;. While this prevents your program from crashing, it completely swallows the error without printing or logging it anywhere. If a critical calculation fails due to bad data, your program will pretend everything is perfect while outputting corrupted data. Always log or display an error message inside your exception blocks instead of leaving them completely empty.

📖 Theory

Enterprise Logs and Next Semester Objects

In live helpdesk management platforms, exception blocks write detailed crash reports into dedicated system error log tables before safely routing users back to safety. You will reuse this exact mindset next semester in your object-oriented courses, where Oracle's EXCEPTION framework transforms directly into standard try-catch blocks found in Java, C++, and Android crash-prevention architectures.

✅ Summary

Key takeaways

• Exceptions are unexpected runtime errors that instantly disrupt normal execution flow.
• Exception handling isolates error correction code away from core application logic blocks.
• Predefined handlers like NO_DATA_FOUND and TOO_MANY_ROWS catch predictable SQL query failures.
• The WHEN OTHERS keyword serves as a final catch-all handler for unexpected system errors.
• Unhandled exceptions propagate upward and terminate database programs prematurely.
• Memory hook: Write your block, check for errors, catch the crash, and log the records!