Exception Handling in PL/SQL: Named System, Unnamed System, User-defined Exceptions

📖 Theory

The Broken Exam Counter

Imagine you are an administrator at a college counter processing student hall tickets. Everything runs smoothly until a student with a missing fee status steps up. If you panic, freeze, and close down the entire registration window, the hundreds of other students waiting in line behind them will suffer. In programming, runtime errors, like dividing by zero, passing a null value, or looking for a student record that has been deleted, are completely inevitable. If your program doesn't know how to react, it panics, crashes the database session, and disconnects the user. Exception handling is our way of telling the system: 'If a specific error trips you up, don't crash the whole application; divert the issue to an emergency handler and keep running normal operations for everyone else!'

📖 Theory

The Household Fuse Box

Think of an exception handler like an electrical fuse box or an MCB (Miniature Circuit Breaker) installed in an Indian home. When a heavy voltage surge or a short circuit happens in a kitchen appliance, the fuse safely blows to cut off current to only that damaged line, protecting your expensive TVs and refrigerators from burning down. In PL/SQL, the EXCEPTION section is that exact safety fuse. It catches high-voltage code failures, neutralizes them cleanly, and keeps the surrounding main database server stable and running.

📖 Theory

The Three Defenses of Runtime Control

An exception is a database warning or error condition raised during execution that interrupts regular instruction flow. PL/SQL neatly classifies these disruptions into three distinct categories based on who discovered the problem and how it gets flagged inside system memory.

📊 At a glance

Structural taxonomy of PL/SQL anomaly management layers

⌨️ Practical

The Complete College Registration and Fee Auditor

-- An anonymous block showcasing the operational differences across all three exception types
DECLARE
  -- A. Declaring a User-Defined exception for institutional rules
  e_low_attendance EXCEPTION;
  
  -- B. Naming an Unnamed System Exception for foreign key check failures (ORA-02292)
  e_child_record_found EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_child_record_found, -2292);
  
  v_name       students.student_name%TYPE;
  v_attendance NUMBER := 68; -- Below university requirement
BEGIN
  -- 1. Simulating a Named System Exception trap
  -- If ID 99999 doesn't exist, this statement instantly triggers NO_DATA_FOUND
  SELECT student_name INTO v_name FROM students WHERE student_id = 99999;
  
  -- 2. Simulating a User-Defined Exception ignition
  IF v_attendance < 75 THEN
    RAISE e_low_attendance;
  END IF;
  
EXCEPTION
  -- Handling Named System Errors
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Catch 1: That student ID was not found in the university registry.');
    
  -- Handling Custom Business Errors
  WHEN e_low_attendance THEN
    DBMS_OUTPUT.PUT_LINE('Catch 2: Admit Card Denied! Attendance is lower than the mandatory 75%.');
    
  -- Handling Unnamed System Errors mapped by hand
  WHEN e_child_record_found THEN
    DBMS_OUTPUT.PUT_LINE('Catch 3: Integrity Error! Cannot wipe course because active students are tied to it.');
    
  -- Global safety catch-all trap
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Catch 4: An unhandled anomaly occurred. System Code: ' || SQLCODE);
END;
/

🪜 Follow along

The Lifecycle of a User-Defined Exception

1. 1. The Blueprint (DECLARE) The developer registers a new custom exception identifier name inside the local block, informing the compiler to track it as a unique error signature.
2. 2. The Rule Check (IF) The operational program checks business logic variables to detect conditions that violate custom domain requirements.
3. 3. The Spark (RAISE) The code encounters a violation and explicitly issues the RAISE command followed by the exception's name, halting the execution sequence immediately.
4. 4. The Interception (WHEN) The execution pointer abandons the remaining lines of code, drops instantly down into the EXCEPTION block, matches the error token, and completes the safety code.

🧩 Quiz

Which PL/SQL programmatic instruction binds a non-predefined numeric Oracle server error code (like ORA-02292) to a developer's custom named exception token?

1. RAISE_APPLICATION_ERROR
2. PRAGMA EXCEPTION_INIT
3. WHEN OTHERS THEN
4. ALTER EXCEPTION TYPE

⚠️ Watch out

The Dangerous Silent Killer: WHEN OTHERS THEN NULL

Pay close attention for lab examinations! Many students have a habit of writing WHEN OTHERS THEN NULL; at the end of their blocks just to bypass error screens. This is a highly dangerous real-world habit. Writing THEN NULL builds a programming black hole, it swallows every unexpected error, data corruption, or memory failure silently without logging it. The user thinks their action succeeded, while the data on disk is broken, making debugging completely impossible!

💭 Think first

The One-Way Execution Trap Check

If an error is intercepted and handled by a matching exception block, does the execution pointer jump back up to finish the remaining lines inside the main BEGIN section?

📖 Theory

External Viva Scoring Secret

When facing external university evaluators during lab exams, they love asking: 'What built-in functions can read error details inside a WHEN OTHERS handle?' Impress them immediately by naming `SQLCODE` (which returns the active negative error number) and `SQLERRM` (which returns the actual descriptive text explanation of the error message).

✅ Summary

Key takeaways

• Exceptions are runtime error anomalies that stop standard program paths from running.
• Named system errors (like NO_DATA_FOUND) are pre-named by the internal Oracle engine.
• Unnamed system errors possess codes but no names; use PRAGMA EXCEPTION_INIT to label them.
• User-defined exceptions enforce custom application rules and must be explicitly triggered with RAISE.
• Once an error fires, execution jumps down to the exception section and never returns back up.
• Use SQLCODE and SQLERRM within a generic WHEN OTHERS catch block to capture unexpected bugs.
• Memory hook: Declare the custom name, check your rules, RAISE the flag if it breaks, and catch it at the WHEN statement!