In Oracle PL/SQL, an exception is an event that disrupts the normal flow of program execution. These events, often referred to as errors or runtime errors, can occur due to various reasons: data issues, system resource problems, logical flaws, or invalid operations. Effective PL/SQL error handling is paramount for building stable and resilient database applications. Without proper exception handling, an unhandled exception will cause the entire PL/SQL block to terminate abruptly, rolling back any uncommitted DML statements, which can lead to incomplete transactions and a poor user experience. Learning how to handle exceptions in Oracle is a core skill for any database programmer.

Note: When developing PL/SQL applications, understanding exception types, exception propagation, and best practices for error management are key concepts. We will explore how to trap errors and implement graceful error recovery using the EXCEPTION block, a fundamental component of structured PL/SQL programming. This approach helps in building fault-tolerant PL/SQL code and ensuring data consistency.

Example 1: Oracle PL/SQL Basic Arithmetic Exception

DECLARE
  v_numerator   NUMBER := 10;
  v_denominator NUMBER := 0;
  v_result      NUMBER;
BEGIN
  -- Attempting a division by zero, which will raise a predefined exception
  v_result := v_numerator / v_denominator;
  DBMS_OUTPUT.PUT_LINE('Result: ' || v_result); -- This line will not be executed
EXCEPTION
  -- Catching the specific predefined exception for division by zero
  WHEN ZERO_DIVIDE THEN
    DBMS_OUTPUT.PUT_LINE('Error: Division by zero occurred. Please check the denominator.');
  -- Catching any other unexpected errors
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('An unexpected error occurred: ' || SQLERRM);
END;
/

Explanation This example demonstrates a common predefined exception: ZERO_DIVIDE. The program attempts to divide 10 by 0, which is an invalid arithmetic operation. Instead of crashing, the EXCEPTION block WHEN ZERO_DIVIDE clause traps the error, printing a user-friendly message. This showcases the basic structure of PL/SQL error trapping and how exceptions prevent program termination.

Example 2: Oracle PL/SQL Invalid Number Conversion Exception

DECLARE
  v_string_num VARCHAR2(20) := 'abc';
  v_number_val NUMBER;
BEGIN
  -- Attempting to convert a non-numeric string to a number, raising a VALUE_ERROR
  v_number_val := TO_NUMBER(v_string_num);
  DBMS_OUTPUT.PUT_LINE('Converted number: ' || v_number_val); -- This line will not be executed
EXCEPTION
  -- Catching the specific predefined exception for data type conversion errors
  WHEN VALUE_ERROR THEN
    DBMS_OUTPUT.PUT_LINE('Error: Invalid number format. Could not convert "' || v_string_num || '" to a number.');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('An unexpected error occurred: ' || SQLERRM);
END;
/

Explanation Here, the code tries to convert the string 'abc' into a number using TO_NUMBER. Since 'abc' is not a valid numerical representation, Oracle raises a VALUE_ERROR exception. The EXCEPTION section WHEN VALUE_ERROR clause handles this data type conversion error, providing a specific error message. This illustrates how PL/SQL exception handling helps in managing data validation issues.

Example 3: Oracle PL/SQL Handling Too Many Rows Exception

DECLARE
  v_employee_name VARCHAR2(100);
BEGIN
  -- Attempting to select a single value into a variable when multiple rows exist
  -- This will raise the TOO_MANY_ROWS exception
  SELECT full_name INTO v_employee_name FROM employees WHERE department_id = 10;
  DBMS_OUTPUT.PUT_LINE('Employee name: ' || v_employee_name); -- This line will not be executed
EXCEPTION
  -- Catching the specific predefined exception for multiple row fetches into a scalar variable
  WHEN TOO_MANY_ROWS THEN
    DBMS_OUTPUT.PUT_LINE('Error: Multiple employees found for department 10. Cannot assign to a single variable.');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('An unexpected error occurred: ' || SQLERRM);
END;
/

Explanation This example demonstrates the TOO_MANY_ROWS exception, which occurs when a SELECT INTO statement returns more than one row but is expected to return only one. The EXCEPTION block specifically catches this scenario, preventing the program from failing and informing the user about the data retrieval issue. This is a critical aspect of PL/SQL SELECT INTO error handling.

Example 4: Oracle PL/SQL Handling No Data Found Exception

DECLARE
  v_product_name VARCHAR2(100);
BEGIN
  -- Attempting to select a row that does not exist based on the product_id
  -- This will raise the NO_DATA_FOUND exception
  SELECT product_name INTO v_product_name FROM products WHERE product_id = 9999;
  DBMS_OUTPUT.PUT_LINE('Product name: ' || v_product_name); -- This line will not be executed
EXCEPTION
  -- Catching the specific predefined exception when no rows are returned
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Error: Product with ID 9999 not found in the database.');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('An unexpected error occurred: ' || SQLERRM);
END;
/

Explanation The NO_DATA_FOUND exception is demonstrated here. It arises when a SELECT INTO statement yields no rows. The EXCEPTION block provides a graceful way to handle this situation, preventing a crash and allowing the program to respond appropriately, such as informing the user that the requested data does not exist. This is essential for robust PL/SQL query handling.

Example 5: Oracle PL/SQL Basic Custom Message with OTHERS

DECLARE
  v_number1 NUMBER := 10;
  v_number2 NUMBER := 0;
  v_result  NUMBER;
BEGIN
  -- This block simulates a scenario where an unexpected error might occur
  -- (e.g., if v_number2 was still 0 but we didn't specifically handle ZERO_DIVIDE)
  -- For demonstration, let's explicitly raise an unhandled exception for OTHERS
  v_result := v_number1 / v_number2; -- This will cause ZERO_DIVIDE, but we'll use OTHERS to catch it
  DBMS_OUTPUT.PUT_LINE('Result: ' || v_result);
EXCEPTION
  -- Using OTHERS to catch any exception not explicitly handled, providing SQLCODE and SQLERRM
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('An unforeseen error has occurred.');
    DBMS_OUTPUT.PUT_LINE('Error Code: ' || SQLCODE); -- Displays Oracle error number
    DBMS_OUTPUT.PUT_LINE('Error Message: ' || SQLERRM); -- Displays Oracle error message
END;
/

Explanation This example focuses on the WHEN OTHERS clause, which acts as a catch-all for any exceptions not explicitly handled by preceding WHEN clauses. It's a best practice to include WHEN OTHERS at the end of your EXCEPTION block to prevent any unhandled exceptions from terminating your program. It also demonstrates the use of SQLCODE (the Oracle error number) and SQLERRM (the Oracle error message) functions, which are invaluable for PL/SQL error logging and debugging to understand what went wrong. This provides a mechanism for generic error handling and error reporting in PL/SQL.

Understanding Exceptions

Understanding Exceptions in PL/SQL involves recognizing the two main categories: predefined exceptions and user-defined exceptions. Predefined exceptions are automatically raised by Oracle when specific runtime errors occur, such as NO_DATA_FOUND or TOO_MANY_ROWS. These have predefined names and error numbers. User-defined exceptions, on the other hand, are declared by the programmer and explicitly raised using the RAISE statement, allowing for custom error conditions tailored to business logic. Grasping these distinctions is fundamental for effective PL/SQL error management and implementing robust exception handling strategies. Knowing when to use predefined vs. custom exceptions is key to writing clean and maintainable code.

Note: A deeper dive into PL/SQL exception types involves understanding their behavior: how they are raised, how they propagate up the call stack if not handled, and the impact they have on transaction integrity. For efficient debugging PL/SQL errors, knowing the common predefined exceptions and how to interpret SQLCODE and SQLERRM is essential. This section will reinforce Oracle PL/SQL error concepts and prepare you for advanced exception handling techniques.

Example 1: Oracle PL/SQL Understanding NO_DATA_FOUND

DECLARE
  v_employee_id NUMBER := 1000; -- Assuming this ID does not exist in employees table
  v_employee_name VARCHAR2(100);
BEGIN
  -- Attempt to retrieve an employee name for a non-existent ID
  -- This will explicitly raise NO_DATA_FOUND
  SELECT first_name || ' ' || last_name
  INTO v_employee_name
  FROM employees
  WHERE employee_id = v_employee_id;

  DBMS_OUTPUT.PUT_LINE('Employee Found: ' || v_employee_name);
EXCEPTION
  -- Specific handling for NO_DATA_FOUND
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Understanding NO_DATA_FOUND: Employee ID ' || v_employee_id || ' does not exist.');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('An unexpected error occurred: ' || SQLERRM);
END;
/

Explanation This example demonstrates the NO_DATA_FOUND predefined exception. When the SELECT INTO statement fails to find any row matching employee_id = 1000, Oracle automatically raises this exception. The EXCEPTION block specifically catches it, preventing the program from terminating and providing a clear message that the record was not found, illustrating how PL/SQL handles missing data.

Example 2: Oracle PL/SQL Understanding TOO_MANY_ROWS

DECLARE
  v_department_id NUMBER := 90; -- Assuming department 90 has multiple employees
  v_manager_id    NUMBER;
BEGIN
  -- Attempt to retrieve a single manager ID for a department with multiple employees
  -- This will explicitly raise TOO_MANY_ROWS
  SELECT manager_id
  INTO v_manager_id
  FROM employees
  WHERE department_id = v_department_id;

  DBMS_OUTPUT.PUT_LINE('Manager ID: ' || v_manager_id);
EXCEPTION
  -- Specific handling for TOO_MANY_ROWS
  WHEN TOO_MANY_ROWS THEN
    DBMS_OUTPUT.PUT_LINE('Understanding TOO_MANY_ROWS: Multiple managers found for department ' || v_department_id || '.');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('An unexpected error occurred: ' || SQLERRM);
END;
/

Explanation Here, the SELECT INTO statement tries to assign multiple manager IDs (because department 90 has multiple employees) to a single scalar variable v_manager_id. This causes Oracle to raise the TOO_MANY_ROWS exception. The EXCEPTION block's WHEN TOO_MANY_ROWS clause catches this, explaining that the query returned more data than expected for a single-variable assignment, crucial for PL/SQL query design.

Example 3: Oracle PL/SQL Understanding DUP_VAL_ON_INDEX

-- Assume a table 'categories' with a unique index on 'category_name'
-- CREATE TABLE categories (
--    category_id NUMBER PRIMARY KEY,
--    category_name VARCHAR2(50) UNIQUE
-- );
-- INSERT INTO categories (category_id, category_name) VALUES (1, 'Electronics');

DECLARE
  v_new_category_id   NUMBER := 2;
  v_new_category_name VARCHAR2(50) := 'Electronics'; -- Duplicate value for unique index
BEGIN
  -- Attempt to insert a duplicate value into a unique indexed column
  -- This will explicitly raise DUP_VAL_ON_INDEX
  INSERT INTO categories (category_id, category_name)
  VALUES (v_new_category_id, v_new_category_name);
  COMMIT;
  DBMS_OUTPUT.PUT_LINE('Category inserted successfully.');
EXCEPTION
  -- Specific handling for DUP_VAL_ON_INDEX
  WHEN DUP_VAL_ON_INDEX THEN
    DBMS_OUTPUT.PUT_LINE('Understanding DUP_VAL_ON_INDEX: Category name "' || v_new_category_name || '" already exists.');
    ROLLBACK; -- Rollback the failed transaction
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('An unexpected error occurred: ' || SQLERRM);
END;
/

Explanation This example illustrates the DUP_VAL_ON_INDEX predefined exception, which occurs when an INSERT or UPDATE statement attempts to store a duplicate value in a column that has a unique index or primary key constraint. The EXCEPTION block catches this specific data integrity violation, preventing the transaction from committing and explaining the reason for the failure, vital for PL/SQL data integrity enforcement.

Example 4: Oracle PL/SQL Understanding PROGRAM_ERROR

DECLARE
  TYPE t_rec IS RECORD (
    id   NUMBER,
    name VARCHAR2(10)
  );
  v_rec t_rec;
BEGIN
  -- Simulating a situation that might lead to PROGRAM_ERROR
  -- This specific example might not always raise PROGRAM_ERROR in all environments
  -- as it's typically for internal PL/SQL issues, but conceptually demonstrates it.
  -- A more common scenario is bad memory access or internal PL/SQL engine issues.
  v_rec.name := RPAD('A', 20, 'B'); -- Trying to assign a string longer than VARCHAR2(10)
  DBMS_OUTPUT.PUT_LINE('Record Name: ' || v_rec.name);
EXCEPTION
  -- Catching PROGRAM_ERROR (often indicates internal PL/SQL errors)
  WHEN PROGRAM_ERROR THEN
    DBMS_OUTPUT.PUT_LINE('Understanding PROGRAM_ERROR: An internal PL/SQL error occurred.');
    DBMS_OUTPUT.PUT_LINE('Details: ' || SQLERRM);
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('An unexpected error occurred: ' || SQLERRM);
END;
/

Explanation The PROGRAM_ERROR exception is less common in typical application code, as it usually signals an internal PL/SQL problem, such as memory corruption or issues with the PL/SQL engine itself. In this example, attempting to assign a string longer than the record field's declared length might sometimes trigger PROGRAM_ERROR (though VALUE_ERROR is more typical for string truncation). Handling it, even if rare, provides robustness against unexpected system-level issues, crucial for advanced PL/SQL debugging and system stability.

Example 5: Oracle PL/SQL Understanding LOGIN_DENIED

-- This example cannot be directly executed within a single anonymous block
-- as it requires attempting a database login.
-- However, we can simulate the scenario conceptually for understanding.

-- Conceptual understanding:
-- IF attempting to connect with invalid credentials, Oracle will raise LOGIN_DENIED.
-- In a real application, this would be handled at the connection layer,
-- but PL/SQL blocks might encounter it if they try to establish a new
-- session or proxy connection within the database using invalid credentials.

/*
BEGIN
  -- Simulate a login attempt within a PL/SQL block (conceptual)
  -- This specific syntax is not valid for direct execution as a login.
  -- It's for understanding the type of error.
  EXECUTE IMMEDIATE 'CONNECT invalid_user/invalid_password';
EXCEPTION
  WHEN LOGIN_DENIED THEN
    DBMS_OUTPUT.PUT_LINE('Understanding LOGIN_DENIED: Authentication failed due to invalid credentials.');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('An unexpected error occurred during login simulation: ' || SQLERRM);
END;
/
*/

-- Actual executable example to demonstrate a similar error, e.g., missing privileges.
-- Although not strictly LOGIN_DENIED, it falls under security-related access issues.
DECLARE
  v_count NUMBER;
BEGIN
  -- Attempting to query a table for which the current user might not have privileges
  -- This could lead to ORA-00942 (table or view does not exist) or ORA-01031 (insufficient privileges)
  -- The closest predefined exception in PL/SQL that might cover this broadly is OTHERS.
  -- A direct LOGIN_DENIED would happen at the connection level.
  SELECT COUNT(*) INTO v_count FROM dba_users; -- Requires DBA role or specific grants
  DBMS_OUTPUT.PUT_LINE('Number of DBA Users: ' || v_count);
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE = -942 THEN
      DBMS_OUTPUT.PUT_LINE('Error ORA-00942: Table or view does not exist, or insufficient privileges.');
    ELSIF SQLCODE = -1031 THEN
      DBMS_OUTPUT.PUT_LINE('Error ORA-01031: Insufficient privileges to access DBA_USERS.');
    ELSE
      DBMS_OUTPUT.PUT_LINE('An unexpected access error occurred: ' || SQLERRM);
    END IF;
END;
/

Explanation The LOGIN_DENIED exception typically occurs at the database connection level, not usually within a running PL/SQL block. However, if a PL/SQL procedure attempts to establish a new session (e.g., using DBMS_SESSION or EXECUTE IMMEDIATE for a proxy connection) with invalid credentials, this exception could be raised. The executable example provided simulates a related access error (insufficient privileges) to demonstrate how PL/SQL security exceptions are handled, often through the OTHERS clause combined with SQLCODE checks, crucial for database security best practices.

Handling Exceptions with the EXCEPTION Block

The EXCEPTION block is the cornerstone of PL/SQL error handling. It is an optional section in a PL/SQL block (anonymous block, procedure, function, or package body) dedicated to managing exceptions that occur during the execution of the BEGIN...END block. When an exception is raised, either automatically by Oracle or explicitly by the programmer, control immediately transfers to the EXCEPTION section. Inside this section, you define exception handlers using WHEN exception_name THEN clauses, allowing your program to perform error recovery actions instead of crashing. This structured approach to exception management is vital for building robust PL/SQL applications.

Note: The sequence of exception handlers within the EXCEPTION block matters: Oracle evaluates them from top to bottom. Specific handlers should come before generic ones (WHEN OTHERS). Utilizing the EXCEPTION block effectively involves understanding exception scope, exception propagation, and how to use SQLCODE and SQLERRM for detailed error information. This mechanism ensures graceful degradation and allows for PL/SQL error logging and transaction control (e.g., ROLLBACK).

Example 1: Oracle PL/SQL Basic EXCEPTION Block Structure

DECLARE
  v_num1 NUMBER := 10;
  v_num2 NUMBER := 0;
  v_result NUMBER;
BEGIN
  -- Main execution section where operations are performed
  DBMS_OUTPUT.PUT_LINE('Attempting division...');
  v_result := v_num1 / v_num2; -- This will raise ZERO_DIVIDE
  DBMS_OUTPUT.PUT_LINE('Division successful: ' || v_result);
EXCEPTION
  -- EXCEPTION block starts here
  WHEN ZERO_DIVIDE THEN
    -- Handler for ZERO_DIVIDE exception
    DBMS_OUTPUT.PUT_LINE('Caught ZERO_DIVIDE: Cannot divide by zero.');
  WHEN OTHERS THEN
    -- Generic handler for any other unhandled exception
    DBMS_OUTPUT.PUT_LINE('Caught OTHERS: An unexpected error occurred: ' || SQLERRM);
END;
/

Explanation This example demonstrates the fundamental structure of an EXCEPTION block. The BEGIN...END section contains the main logic. When ZERO_DIVIDE occurs, control jumps directly to the EXCEPTION section. The WHEN ZERO_DIVIDE THEN clause catches the specific exception, preventing the program from terminating and allowing for controlled error handling. This is the most basic form of PL/SQL error trapping.

Example 2: Oracle PL/SQL Handling Multiple Exceptions in one block

DECLARE
  v_id         NUMBER := 100;
  v_name       VARCHAR2(50);
  v_salary_str VARCHAR2(10) := 'abc'; -- Will cause VALUE_ERROR
  v_salary     NUMBER;
BEGIN
  -- First potential error: NO_DATA_FOUND
  SELECT employee_name INTO v_name FROM employees WHERE employee_id = 9999; -- Assumed non-existent
  DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_name);

  -- Second potential error: VALUE_ERROR
  v_salary := TO_NUMBER(v_salary_str);
  DBMS_OUTPUT.PUT_LINE('Salary: ' || v_salary);
EXCEPTION
  -- Handler for NO_DATA_FOUND
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Error: No data found for the specified ID.');
  -- Handler for VALUE_ERROR
  WHEN VALUE_ERROR THEN
    DBMS_OUTPUT.PUT_LINE('Error: Invalid data format for salary conversion.');
  -- Generic handler for any other errors
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('An unhandled exception occurred: ' || SQLERRM);
END;
/

Explanation This example showcases how a single EXCEPTION block can handle multiple distinct predefined exceptions. The code first attempts a SELECT INTO that would raise NO_DATA_FOUND, then a TO_NUMBER conversion that would raise VALUE_ERROR. Oracle processes the handlers in order. When VALUE_ERROR is raised by the TO_NUMBER function, control is transferred to WHEN VALUE_ERROR, demonstrating efficient multi-exception handling in PL/SQL.

Example 3: Oracle PL/SQL Using SQLCODE and SQLERRM in EXCEPTION Block

DECLARE
  v_string VARCHAR2(5) := 'Hello World'; -- Too long for VARCHAR2(5)
BEGIN
  DBMS_OUTPUT.PUT_LINE('Original String: ' || v_string); -- This line is executed
  -- Assigning a longer string to a smaller variable will raise VALUE_ERROR (string truncation)
  -- The implicit assignment happens at declaration or first use. Let's make it explicit.
  DECLARE
    v_short_string VARCHAR2(5);
  BEGIN
    v_short_string := 'Hello World'; -- This assignment will raise VALUE_ERROR
  END;
  DBMS_OUTPUT.PUT_LINE('This line will not be reached.');
EXCEPTION
  WHEN VALUE_ERROR THEN
    DBMS_OUTPUT.PUT_LINE('Caught VALUE_ERROR due to string truncation.');
    DBMS_OUTPUT.PUT_LINE('Oracle Error Code: ' || SQLCODE); -- e.g., -6502 for PL/SQL: numeric or value error
    DBMS_OUTPUT.PUT_LINE('Oracle Error Message: ' || SQLERRM); -- Provides the full error description
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('An unexpected error occurred: ' || SQLERRM);
END;
/

Explanation This example demonstrates the critical use of SQLCODE and SQLERRM functions within the EXCEPTION block. When a VALUE_ERROR occurs due to string truncation, SQLCODE returns the specific Oracle error number (e.g., -6502), and SQLERRM returns the associated error message. These functions are indispensable for PL/SQL debugging, error logging, and providing detailed information for troubleshooting PL/SQL runtime errors.

Example 4: Oracle PL/SQL Nested Block Exception Handling

DECLARE
  v_outer_var NUMBER := 10;
BEGIN
  DBMS_OUTPUT.PUT_LINE('Entering outer block.');
  BEGIN -- Nested PL/SQL block
    DECLARE
      v_inner_var NUMBER := 0;
    BEGIN
      DBMS_OUTPUT.PUT_LINE('Entering inner block.');
      v_outer_var := v_outer_var / v_inner_var; -- ZERO_DIVIDE in inner block
      DBMS_OUTPUT.PUT_LINE('Inner block operation successful.');
    EXCEPTION
      WHEN ZERO_DIVIDE THEN
        DBMS_OUTPUT.PUT_LINE('Inner block caught ZERO_DIVIDE.');
        -- If an exception is caught and handled here, it does not propagate further
        v_outer_var := -1; -- Reset variable to a known state
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Inner block caught OTHERS: ' || SQLERRM);
    END; -- End of nested block
  DBMS_OUTPUT.PUT_LINE('Exiting outer block. Outer var: ' || v_outer_var);
EXCEPTION
  WHEN OTHERS THEN
    -- This outer block handler will only be reached if an exception
    -- from the inner block was NOT handled.
    DBMS_OUTPUT.PUT_LINE('Outer block caught OTHERS: ' || SQLERRM);
END;
/

Explanation This advanced example illustrates nested EXCEPTION blocks and exception propagation. An inner block performs a division by zero, which is caught and handled within the inner block's EXCEPTION section. Because the inner block handles the exception, it does not propagate to the outer block, allowing the outer block to continue execution normally. This demonstrates localized error handling and the control flow when exceptions are managed within their scope, a key concept for complex PL/SQL architectures.

Example 5: Oracle PL/SQL Transaction Control with EXCEPTION Block

-- Assume a table 'accounts' with columns account_id, balance
-- CREATE TABLE accounts (
--    account_id NUMBER PRIMARY KEY,
--    balance NUMBER
-- );
-- INSERT INTO accounts (account_id, balance) VALUES (1, 1000);
-- INSERT INTO accounts (account_id, balance) VALUES (2, 500);

DECLARE
  v_from_account NUMBER := 1;
  v_to_account   NUMBER := 2;
  v_amount       NUMBER := 700;
BEGIN
  -- Start of transaction
  UPDATE accounts
  SET balance = balance - v_amount
  WHERE account_id = v_from_account;

  -- Simulate an error condition (e.g., insufficient funds or invalid account)
  -- For demonstration, let's artificially raise a NO_DATA_FOUND if balance drops too low
  -- A more realistic scenario would be a check before update.
  -- Let's cause a VALUE_ERROR to demonstrate rollback
  DECLARE
    v_test_num VARCHAR2(5) := 'INVALID';
    v_converted_num NUMBER;
  BEGIN
    v_converted_num := TO_NUMBER(v_test_num); -- This will raise VALUE_ERROR
  END;

  UPDATE accounts
  SET balance = balance + v_amount
  WHERE account_id = v_to_account;

  COMMIT; -- This will only be reached if no exception occurs
  DBMS_OUTPUT.PUT_LINE('Transaction committed successfully.');

EXCEPTION
  WHEN VALUE_ERROR THEN
    ROLLBACK; -- Rollback all changes made in the transaction
    DBMS_OUTPUT.PUT_LINE('Transaction rolled back due to data conversion error.');
    DBMS_OUTPUT.PUT_LINE('Details: ' || SQLERRM);
  WHEN OTHERS THEN
    ROLLBACK; -- Always rollback on any unexpected error
    DBMS_OUTPUT.PUT_LINE('Transaction rolled back due to an unexpected error.');
    DBMS_OUTPUT.PUT_LINE('Details: ' || SQLERRM);
END;
/

-- Verify accounts table after running
-- SELECT * FROM accounts;

Explanation This example highlights how the EXCEPTION block is crucial for transaction control and ensuring data consistency. The PL/SQL block attempts to simulate a funds transfer. An artificial VALUE_ERROR is introduced to simulate a failure within the transaction. When this exception is caught, the ROLLBACK statement within the EXCEPTION handler undoes all uncommitted DML operations (the initial UPDATE statement in this case), preserving the database's integrity. This is a critical aspect of reliable PL/SQL programming and ACID compliance.

Predefined Exceptions (e.g., NO_DATA_FOUND, TOO_MANY_ROWS)

Predefined exceptions in Oracle PL/SQL are system-defined errors that have dedicated names assigned by Oracle. These exceptions are automatically raised by the Oracle engine when specific runtime conditions occur. They cover a wide range of common database and programming errors, making your PL/SQL error handling more readable and specific than relying solely on generic error codes. Examples include NO_DATA_FOUND (when a SELECT INTO returns no rows), TOO_MANY_ROWS (when a SELECT INTO returns more than one row), DUP_VAL_ON_INDEX (when inserting duplicate values into a unique column), and ZERO_DIVIDE (arithmetic division by zero). Utilizing these named exceptions improves PL/SQL code clarity and maintainability.

Note: Familiarity with the most common Oracle predefined exceptions is essential for any PL/SQL developer. While SQLCODE and SQLERRM can identify any error, using named predefined exceptions allows for more precise and understandable exception handlers. This section will focus on the practical application of these named exceptions, demonstrating how to catch specific errors for more granular PL/SQL error management and effective fault tolerance.

Example 1: Oracle PL/SQL Handling NO_DATA_FOUND for Employee Lookup

DECLARE
  v_employee_id NUMBER := 500; -- Assuming employee_id 500 does not exist
  v_employee_info employees%ROWTYPE;
BEGIN
  -- Attempt to retrieve an entire row for a non-existent employee
  SELECT *
  INTO v_employee_info
  FROM employees
  WHERE employee_id = v_employee_id;

  DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_employee_info.first_name || ' ' || v_employee_info.last_name);
EXCEPTION
  -- Specifically handling the case where no employee is found
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Predefined Exception: NO_DATA_FOUND - Employee with ID ' || v_employee_id || ' does not exist.');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('An unexpected error occurred: ' || SQLERRM);
END;
/

Explanation This example specifically handles the NO_DATA_FOUND predefined exception. When the SELECT * INTO statement fails to locate an employee with ID 500, Oracle automatically raises this exception. The dedicated WHEN NO_DATA_FOUND handler catches it, providing a clear message about the missing data, which is a common scenario in PL/SQL data retrieval.

Example 2: Oracle PL/SQL Handling TOO_MANY_ROWS for Department Manager

DECLARE
  v_department_name VARCHAR2(50) := 'Sales'; -- Assuming multiple employees in Sales
  v_manager_id      NUMBER;
BEGIN
  -- Attempt to find a single manager ID for a department that has multiple
  -- employees, leading to TOO_MANY_ROWS
  SELECT manager_id
  INTO v_manager_id
  FROM employees
  WHERE department_id = (SELECT department_id FROM departments WHERE department_name = v_department_name);

  DBMS_OUTPUT.PUT_LINE('Manager ID for Sales: ' || v_manager_id);
EXCEPTION
  -- Specifically handling the case where more than one row is returned
  WHEN TOO_MANY_ROWS THEN
    DBMS_OUTPUT.PUT_LINE('Predefined Exception: TOO_MANY_ROWS - Multiple employees found for ' || v_department_name || ' department.');
    DBMS_OUTPUT.PUT_LINE('Consider using a cursor or aggregate function for multiple rows.');
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Department ' || v_department_name || ' not found.');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('An unexpected error occurred: ' || SQLERRM);
END;
/

Explanation This example demonstrates the TOO_MANY_ROWS predefined exception. If the subquery or the main query implicitly returns more than one manager_id for the 'Sales' department, this exception is raised. The WHEN TOO_MANY_ROWS clause effectively captures this, advising the developer on how to handle scenarios where multiple records are returned, crucial for PL/SQL cursor management and query result sets.

Example 3: Oracle PL/SQL Handling DUP_VAL_ON_INDEX on User Creation

-- Assume a simple 'app_users' table with a unique constraint on 'username'
-- CREATE TABLE app_users (
--    user_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
--    username VARCHAR2(50) UNIQUE NOT NULL,
--    email VARCHAR2(100)
-- );
-- INSERT INTO app_users (username, email) VALUES ('john.doe', 'john.doe@example.com');

DECLARE
  v_new_username VARCHAR2(50) := 'john.doe'; -- Existing username
  v_new_email    VARCHAR2(100) := 'j.doe@example.com';
BEGIN
  -- Attempt to insert a user with a username that already exists, violating a unique constraint
  INSERT INTO app_users (username, email)
  VALUES (v_new_username, v_new_email);
  COMMIT;
  DBMS_OUTPUT.PUT_LINE('User ' || v_new_username || ' created successfully.');
EXCEPTION
  -- Specifically handling the unique constraint violation
  WHEN DUP_VAL_ON_INDEX THEN
    ROLLBACK;
    DBMS_OUTPUT.PUT_LINE('Predefined Exception: DUP_VAL_ON_INDEX - Username "' || v_new_username || '" already taken. Please choose another.');
  WHEN OTHERS THEN
    ROLLBACK;
    DBMS_OUTPUT.PUT_LINE('An unexpected error occurred during user creation: ' || SQLERRM);
END;
/

Explanation This example illustrates the DUP_VAL_ON_INDEX predefined exception. When an INSERT statement attempts to add a record with a username that already exists in the app_users table (which has a unique constraint on username), this exception is raised. The EXCEPTION handler prevents the insertion, rolls back the transaction, and informs the user about the duplicate key violation, a common requirement for data integrity in PL/SQL.

Example 4: Oracle PL/SQL Handling VALUE_ERROR for Input Validation

DECLARE
  v_age_str VARCHAR2(10) := 'twenty'; -- Non-numeric input
  v_age     NUMBER;
BEGIN
  -- Attempt to convert a non-numeric string to a number
  v_age := TO_NUMBER(v_age_str);
  DBMS_OUTPUT.PUT_LINE('Age: ' || v_age);
EXCEPTION
  -- Specifically handling data conversion errors
  WHEN VALUE_ERROR THEN
    DBMS_OUTPUT.PUT_LINE('Predefined Exception: VALUE_ERROR - Invalid input for age: "' || v_age_str || '". Please enter a numeric value.');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('An unexpected error occurred: ' || SQLERRM);
END;
/

Explanation The VALUE_ERROR predefined exception is demonstrated here when attempting to convert the non-numeric string 'twenty' into a NUMBER type. The EXCEPTION block catches this data conversion issue, allowing the program to gracefully handle invalid user input without crashing. This is a crucial aspect of PL/SQL input validation and robust application design.

Example 5: Oracle PL/SQL Handling ZERO_DIVIDE in Financial Calculation

DECLARE
  v_total_revenue NUMBER := 10000;
  v_total_units   NUMBER := 0; -- Simulating zero units sold
  v_revenue_per_unit NUMBER;
BEGIN
  -- Attempt to calculate revenue per unit when total units is zero
  v_revenue_per_unit := v_total_revenue / v_total_units;
  DBMS_OUTPUT.PUT_LINE('Revenue Per Unit: ' || v_revenue_per_unit);
EXCEPTION
  -- Specifically handling division by zero
  WHEN ZERO_DIVIDE THEN
    DBMS_OUTPUT.PUT_LINE('Predefined Exception: ZERO_DIVIDE - Cannot calculate revenue per unit when total units is zero.');
    DBMS_OUTPUT.PUT_LINE('Consider handling this by setting revenue_per_unit to 0 or NULL, or reporting the error.');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('An unexpected error occurred: ' || SQLERRM);
END;
/

Explanation This example explicitly handles the ZERO_DIVIDE predefined exception. When the calculation attempts to divide v_total_revenue by v_total_units, which is 0, this arithmetic error is raised. The EXCEPTION handler provides a specific message, preventing the program from terminating and guiding the developer on how to manage such mathematical errors in PL/SQL, common in financial applications.