Advanced Exception Handling in Oracle PL/SQL is crucial for building robust and reliable applications. It allows developers to anticipate and manage errors effectively, preventing application crashes and providing meaningful feedback to users. This section covers key advanced techniques for sophisticated error management.
User-Defined Exceptions
User-defined exceptions are custom exceptions declared by the developer to handle specific application or business rule violations that are not covered by Oracle's predefined exceptions. They provide a clear and organized way to manage application-specific errors, leading to more readable and maintainable code. Declaring and raising your own exceptions is a powerful feature for enforcing business logic and ensuring data integrity in your Oracle PL/SQL programs.
Example 1: Declaring and Raising a Simple User-Defined Exception
DECLARE
e_invalid_salary EXCEPTION; -- Declare a user-defined exception
v_employee_salary NUMBER := 500;
BEGIN
-- Check a business rule: salary must be at least 1000
IF v_employee_salary < 1000 THEN
RAISE e_invalid_salary; -- Explicitly raise the user-defined exception
END IF;
DBMS_OUTPUT.PUT_LINE('Employee salary is valid: ' || v_employee_salary);
EXCEPTION
WHEN e_invalid_salary THEN -- Handle the user-defined exception
DBMS_OUTPUT.PUT_LINE('Error: Invalid employee salary. Salary must be at least 1000.');
WHEN OTHERS THEN -- Catch any other unexpected exceptions
DBMS_OUTPUT.PUT_LINE('An unexpected error occurred: ' || SQLERRM);
END;
/
Explanation
This example demonstrates the basic declaration and handling of a user-defined exception, e_invalid_salary. The exception is declared within the DECLARE section. Inside the BEGIN block, a condition (v_employee_salary < 1000) is checked. If the condition is true, the RAISE statement triggers e_invalid_salary. The EXCEPTION section then catches this specific exception using WHEN e_invalid_salary THEN and prints a custom error message, preventing the program from crashing and providing clear feedback. This is a fundamental pattern for custom error management in Oracle PL/SQL.
Example 2: User-Defined Exception in a Stored Procedure
CREATE OR REPLACE PROCEDURE adjust_employee_bonus (
p_employee_id IN NUMBER,
p_bonus_percentage IN NUMBER
)
IS
e_negative_bonus_percentage EXCEPTION; -- Declare a user-defined exception for invalid input
v_current_bonus NUMBER;
BEGIN
-- Validate input parameter for business rule
IF p_bonus_percentage < 0 THEN
RAISE e_negative_bonus_percentage;
END IF;
-- Simulate fetching current bonus (in a real scenario, this would be a SELECT statement)
SELECT bonus_amount INTO v_current_bonus FROM employees WHERE employee_id = p_employee_id; -- Assuming 'employees' table exists with 'bonus_amount'
-- Simulate updating bonus based on percentage
-- UPDATE employees SET bonus_amount = bonus_amount * (1 + p_bonus_percentage / 100) WHERE employee_id = p_employee_id;
DBMS_OUTPUT.PUT_LINE('Adjusting bonus for employee ' || p_employee_id || ' by ' || p_bonus_percentage || '%.');
DBMS_OUTPUT.PUT_LINE('New estimated bonus would be: ' || (v_current_bonus * (1 + p_bonus_percentage / 100)));
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Error: Employee ' || p_employee_id || ' not found.');
WHEN e_negative_bonus_percentage THEN
DBMS_OUTPUT.PUT_LINE('Error: Bonus percentage cannot be negative. Please provide a positive value.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An unexpected error occurred while adjusting bonus: ' || SQLERRM);
END;
/
-- Test cases
BEGIN
DBMS_OUTPUT.PUT_LINE('--- Test Case 1: Valid Bonus ---');
adjust_employee_bonus(101, 10); -- Assuming employee 101 exists and has a bonus_amount
DBMS_OUTPUT.PUT_LINE('--- Test Case 2: Negative Bonus Percentage ---');
adjust_employee_bonus(102, -5);
DBMS_OUTPUT.PUT_LINE('--- Test Case 3: Employee Not Found ---');
adjust_employee_bonus(999, 5);
END;
/
Explanation
This example integrates a user-defined exception, e_negative_bonus_percentage, within a stored procedure. The procedure adjust_employee_bonus validates the input p_bonus_percentage. If the percentage is negative, the custom exception is raised, ensuring that invalid business data is not processed. This demonstrates how user-defined exceptions are fundamental for implementing data validation and business rules directly within your Oracle database procedures, providing robust PL/SQL error handling.
Example 3: Handling Multiple User-Defined Exceptions
DECLARE
e_invalid_quantity EXCEPTION;
e_product_out_of_stock EXCEPTION;
v_product_id NUMBER := 10;
v_requested_qty NUMBER := 15;
v_available_qty NUMBER := 10; -- Simulate available stock
BEGIN
IF v_requested_qty <= 0 THEN
RAISE e_invalid_quantity;
END IF;
IF v_requested_qty > v_available_qty THEN
RAISE e_product_out_of_stock;
END IF;
DBMS_OUTPUT.PUT_LINE('Order for product ' || v_product_id || ' with quantity ' || v_requested_qty || ' processed successfully.');
EXCEPTION
WHEN e_invalid_quantity THEN
DBMS_OUTPUT.PUT_LINE('Error: The requested quantity must be a positive number.');
WHEN e_product_out_of_stock THEN
DBMS_OUTPUT.PUT_LINE('Error: Product ' || v_product_id || ' is out of stock. Available quantity: ' || v_available_qty);
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An unexpected error occurred: ' || SQLERRM);
END;
/
Explanation
This code demonstrates how to declare and handle multiple distinct user-defined exceptions within a single PL/SQL block. It checks for two different conditions: an invalid requested quantity (e_invalid_quantity) and insufficient stock (e_product_out_of_stock). Each exception is caught by its specific WHEN clause in the EXCEPTION section, allowing for tailored error messages and specific recovery actions. This structured approach to custom exception handling is vital for managing diverse business rule violations in complex Oracle PL/SQL applications.
Example 4: User-Defined Exception with Contextual Information
DECLARE
e_data_integrity_issue EXCEPTION;
v_order_id NUMBER := 12345;
v_status VARCHAR2(20);
BEGIN
-- Simulate a scenario where an order has an unexpected status, violating data integrity
SELECT 'PENDING' INTO v_status FROM DUAL; -- In a real application, this would be a table lookup
IF v_status NOT IN ('COMPLETED', 'SHIPPED', 'CANCELLED') THEN
-- If an order is in an unexpected state, raise a custom exception
RAISE e_data_integrity_issue;
END IF;
DBMS_OUTPUT.PUT_LINE('Order ' || v_order_id || ' has valid status: ' || v_status);
EXCEPTION
WHEN e_data_integrity_issue THEN
DBMS_OUTPUT.PUT_LINE('Data integrity error for order ID ' || v_order_id || '. Invalid status: ' || v_status || '. Please investigate.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An unexpected system error occurred: ' || SQLERRM);
END;
/
Explanation
This example illustrates using a user-defined exception, e_data_integrity_issue, to signal a data validation problem. While the exception itself doesn't carry parameters, the exception handler uses the context (variables v_order_id and v_status) available in the block to provide a highly informative error message. This demonstrates a common pattern in Oracle PL/SQL programming: catching a custom error and then using the current state of variables to log or report detailed information about the failure, which is critical for debugging and maintenance.
Example 5: User-Defined Exception in a Function for Validation
CREATE OR REPLACE FUNCTION calculate_discount (
p_item_price IN NUMBER,
p_discount_rate IN NUMBER
) RETURN NUMBER
IS
e_invalid_discount_rate EXCEPTION; -- Declare custom exception for validation
v_calculated_discount NUMBER;
BEGIN
-- Business rule: Discount rate must be between 0 and 1 (0% to 100%)
IF p_discount_rate < 0 OR p_discount_rate > 1 THEN
RAISE e_invalid_discount_rate;
END IF;
v_calculated_discount := p_item_price * p_discount_rate;
RETURN v_calculated_discount;
EXCEPTION
WHEN e_invalid_discount_rate THEN
DBMS_OUTPUT.PUT_LINE('Error in calculate_discount: Discount rate ' || p_discount_rate || ' is out of valid range (0 to 1).');
-- Re-raise to propagate the error or return a specific value
-- RAISE; -- Option to propagate the exception
RETURN NULL; -- Or return a specific error indicator
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An unexpected error occurred in calculate_discount: ' || SQLERRM);
RETURN NULL;
END;
/
-- Test cases
DECLARE
v_discount NUMBER;
BEGIN
DBMS_OUTPUT.PUT_LINE('--- Test Case 1: Valid Discount ---');
v_discount := calculate_discount(100, 0.20);
IF v_discount IS NOT NULL THEN
DBMS_OUTPUT.PUT_LINE('Calculated discount: ' || v_discount);
END IF;
DBMS_OUTPUT.PUT_LINE('--- Test Case 2: Invalid Discount Rate (Too High) ---');
v_discount := calculate_discount(100, 1.5);
IF v_discount IS NOT NULL THEN
DBMS_OUTPUT.PUT_LINE('Calculated discount: ' || v_discount);
END IF;
DBMS_OUTPUT.PUT_LINE('--- Test Case 3: Invalid Discount Rate (Negative) ---');
v_discount := calculate_discount(100, -0.10);
IF v_discount IS NOT NULL THEN
DBMS_OUTPUT.PUT_LINE('Calculated discount: ' || v_discount);
END IF;
END;
/
Explanation
This example demonstrates a user-defined exception (e_invalid_discount_rate) used within a PL/SQL function to enforce input validation. The calculate_discount function checks if the p_discount_rate is within an acceptable range. If not, the custom exception is raised. In the exception handler, a custom message is printed, and NULL is returned, signifying an error to the caller without crashing the program. This highlights how user-defined exceptions are integral to creating robust, self-validating Oracle PL/SQL functions, crucial for reliable business logic implementation.
The RAISE Statement
The RAISE statement is a fundamental construct in Oracle PL/SQL for explicitly raising an exception. You can use RAISE to trigger either a predefined Oracle exception (like NO_DATA_FOUND or TOO_MANY_ROWS) or a user-defined exception you have declared. When RAISE is executed, control immediately transfers to the EXCEPTION section of the current block. If no handler for that specific exception is found, the exception propagates up the calling stack to the enclosing block or calling program. The RAISE; (without an exception name) statement is used within an exception handler to re-raise the current exception, propagating it further up the call stack, which is useful for logging errors at one level and then letting a higher level handle the recovery.
Example 1: Raising a Predefined Exception
DECLARE
v_employee_name VARCHAR2(100);
BEGIN
-- Simulate a scenario where a SELECT INTO statement might return no rows
SELECT first_name INTO v_employee_name
FROM employees
WHERE employee_id = 99999; -- An employee ID that is highly unlikely to exist
-- This line will not be reached if NO_DATA_FOUND occurs
DBMS_OUTPUT.PUT_LINE('Employee found: ' || v_employee_name);
EXCEPTION
WHEN NO_DATA_FOUND THEN
-- Explicitly re-raise NO_DATA_FOUND to demonstrate RAISE with predefined exception
DBMS_OUTPUT.PUT_LINE('Handling: Employee not found in the initial select.');
RAISE NO_DATA_FOUND; -- Re-raising the same exception explicitly
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An unexpected error occurred: ' || SQLERRM);
END;
/
Explanation
This example shows how a predefined exception, NO_DATA_FOUND, can be caught and then explicitly re-raised using the RAISE statement. The SELECT INTO statement is designed to fail, triggering NO_DATA_FOUND. Within the EXCEPTION block, after some custom handling (like logging), RAISE NO_DATA_FOUND; is used. This causes the exception to propagate further if there was an outer block to catch it. While this specific example demonstrates re-raising the same exception that just occurred, RAISE can be used to raise any predefined exception at any point, providing fine-grained control over error flow in Oracle PL/SQL.
Example 2: Raising a User-Defined Exception with RAISE
DECLARE
e_order_too_large EXCEPTION;
v_total_order_value NUMBER := 15000;
c_max_order_value CONSTANT NUMBER := 10000;
BEGIN
IF v_total_order_value > c_max_order_value THEN
RAISE e_order_too_large; -- Raise the user-defined exception
END IF;
DBMS_OUTPUT.PUT_LINE('Order value ' || v_total_order_value || ' is within limits. Processing order.');
EXCEPTION
WHEN e_order_too_large THEN
DBMS_OUTPUT.PUT_LINE('Error: The total order value (' || v_total_order_value || ') exceeds the maximum allowed (' || c_max_order_value || ').');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An unexpected error occurred: ' || SQLERRM);
END;
/
Explanation
This example demonstrates the standard use of the RAISE statement to trigger a user-defined exception, e_order_too_large. A business rule (maximum order value) is checked, and if violated, RAISE e_order_too_large; immediately transfers control to the EXCEPTION block. This is the primary mechanism for signaling application-specific errors within your PL/SQL code, allowing for custom error messages and ensuring that business constraints are enforced, which is a cornerstone of effective Oracle PL/SQL error handling.
Example 3: Re-raising the Current Exception (RAISE;)
DECLARE
v_product_id NUMBER := 100;
v_price NUMBER;
BEGIN
BEGIN -- Inner block
SELECT price INTO v_price FROM products WHERE product_id = 99999; -- Product ID unlikely to exist
DBMS_OUTPUT.PUT_LINE('Price: ' || v_price);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Inner block caught NO_DATA_FOUND for product ID: ' || v_product_id);
RAISE; -- Re-raise the current exception (NO_DATA_FOUND)
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Inner block caught unexpected error: ' || SQLERRM);
RAISE; -- Re-raise any other unexpected error
END;
DBMS_OUTPUT.PUT_LINE('This line will not be reached if exception propagates from inner block.');
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Outer block caught propagated NO_DATA_FOUND.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Outer block caught an unexpected propagated error: ' || SQLERRM);
END;
/
Explanation
This critical example showcases the RAISE; statement (without an exception name), which re-raises the exception that just occurred in the current exception handler. Here, an inner block attempts a SELECT INTO that will result in NO_DATA_FOUND. The inner block's EXCEPTION section catches it, prints a message, and then uses RAISE;. This causes the NO_DATA_FOUND exception to propagate to the outer block, where it is caught and handled again. This pattern is invaluable for error logging at a lower level while allowing a higher-level caller to perform recovery actions, enabling complex, multi-layered error management in Oracle PL/SQL.
Example 4: Using RAISE in a Loop for Conditional Exit
DECLARE
e_critical_value_found EXCEPTION;
TYPE number_array IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
l_numbers number_array;
BEGIN
l_numbers(1) := 10;
l_numbers(2) := 20;
l_numbers(3) := 5; -- This is the "critical" value
l_numbers(4) := 30;
l_numbers(5) := 40;
FOR i IN 1..l_numbers.COUNT LOOP
DBMS_OUTPUT.PUT_LINE('Processing number: ' || l_numbers(i));
IF l_numbers(i) < 10 THEN
RAISE e_critical_value_found; -- Stop processing and raise exception
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('All numbers processed successfully.');
EXCEPTION
WHEN e_critical_value_found THEN
DBMS_OUTPUT.PUT_LINE('Processing stopped: A critical value (less than 10) was found.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An unexpected error occurred: ' || SQLERRM);
END;
/
Explanation
This example demonstrates using the RAISE statement within a loop to immediately exit the loop and signal an error condition. When the loop encounters a number less than 10 (our "critical value"), RAISE e_critical_value_found; is executed. This immediately terminates the loop and transfers control to the EXCEPTION block, preventing further processing. This technique is highly effective for enforcing specific conditions or validations within iterative processes, providing a clear and efficient way to handle exceptions that require immediate termination in Oracle PL/SQL programming.
Example 5: Combining RAISE with IF for Business Logic
DECLARE
e_transaction_denied EXCEPTION;
v_account_balance NUMBER := 500;
v_withdrawal_amount NUMBER := 700;
BEGIN
-- Business rule: Cannot withdraw more than available balance
IF v_withdrawal_amount > v_account_balance THEN
RAISE e_transaction_denied; -- Raise custom exception
END IF;
-- Simulate transaction processing
DBMS_OUTPUT.PUT_LINE('Transaction successful. New balance: ' || (v_account_balance - v_withdrawal_amount));
EXCEPTION
WHEN e_transaction_denied THEN
DBMS_OUTPUT.PUT_LINE('Error: Withdrawal amount (' || v_withdrawal_amount || ') exceeds account balance (' || v_account_balance || '). Transaction denied.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An unexpected error occurred during transaction: ' || SQLERRM);
END;
/
Explanation
This example clearly illustrates how RAISE is used in conjunction with an IF statement to enforce a fundamental business rule: preventing withdrawals that exceed the account balance. When v_withdrawal_amount is greater than v_account_balance, RAISE e_transaction_denied; is executed. This immediately stops the normal flow and signals the specific business error, allowing the EXCEPTION block to provide a user-friendly message. This pattern is fundamental for building secure and reliable financial or transactional systems using Oracle PL/SQL, ensuring robust application error handling.
The RAISE_APPLICATION_ERROR Procedure
The RAISE_APPLICATION_ERROR procedure is a powerful built-in Oracle PL/SQL function that allows you to issue user-defined error messages from stored procedures, functions, or triggers. It raises an exception and returns a user-defined error number (which must be between -20000 and -20999) and a custom error message to the calling environment. This is particularly useful for communicating application-specific errors back to client applications (like Java, C#, or front-end tools), providing more descriptive error handling than generic "ORA-06510: PL/SQL: unhandled user-defined exception" messages. Using RAISE_APPLICATION_ERROR is a best practice for structured error communication in Oracle database programming.
Example 1: Basic Use of RAISE_APPLICATION_ERROR
DECLARE
v_quantity_on_hand NUMBER := 5;
v_order_quantity NUMBER := 10;
BEGIN
IF v_order_quantity > v_quantity_on_hand THEN
-- Raise a user-defined application error with a specific error code and message
RAISE_APPLICATION_ERROR(-20001, 'Insufficient stock. Only ' || v_quantity_on_hand || ' units available.');
END IF;
DBMS_OUTPUT.PUT_LINE('Order processed for ' || v_order_quantity || ' units.');
EXCEPTION
-- This block will catch the error raised by RAISE_APPLICATION_ERROR
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Caught error: ' || SQLCODE || ' - ' || SQLERRM);
END;
/
Explanation
This example demonstrates the simplest form of RAISE_APPLICATION_ERROR. When the v_order_quantity exceeds v_quantity_on_hand, the procedure is called with error code -20001 and a descriptive message. The PL/SQL block then catches this error in its WHEN OTHERS handler, which displays both the SQLCODE (which will be -20001) and SQLERRM (our custom message). This illustrates how to generate custom, numbered error messages for robust Oracle PL/SQL error handling, making it easier for calling applications to interpret specific business logic failures.
Example 2: RAISE_APPLICATION_ERROR in a Stored Procedure for Business Validation
CREATE OR REPLACE PROCEDURE register_new_customer (
p_customer_name IN VARCHAR2,
p_email_address IN VARCHAR2
)
IS
v_email_exists_count NUMBER;
BEGIN
-- Check if email already exists (business rule)
SELECT COUNT(*)
INTO v_email_exists_count
FROM customers
WHERE email_address = p_email_address; -- Assuming a 'customers' table exists
IF v_email_exists_count > 0 THEN
-- Raise a custom application error if email is not unique
RAISE_APPLICATION_ERROR(-20002, 'Customer registration failed: Email address ' || p_email_address || ' already exists.');
END IF;
-- Simulate inserting new customer
-- INSERT INTO customers (customer_name, email_address) VALUES (p_customer_name, p_email_address);
DBMS_OUTPUT.PUT_LINE('Customer ' || p_customer_name || ' registered successfully with email ' || p_email_address || '.');
EXCEPTION
WHEN OTHERS THEN
-- Catch and report any error, including those raised by RAISE_APPLICATION_ERROR
DBMS_OUTPUT.PUT_LINE('Error registering customer: ' || SQLERRM);
-- Optionally re-raise the error for the calling environment
-- RAISE;
END;
/
-- Test cases
BEGIN
-- Assuming no customer with test@example.com exists initially
DBMS_OUTPUT.PUT_LINE('--- Test Case 1: First Registration ---');
register_new_customer('Alice Smith', 'test@example.com');
DBMS_OUTPUT.PUT_LINE('--- Test Case 2: Duplicate Registration ---');
register_new_customer('Bob Johnson', 'test@example.com'); -- This will trigger the error
END;
/
Explanation
This example demonstrates RAISE_APPLICATION_ERROR within a stored procedure to enforce a business rule: unique email addresses for customer registration. If a duplicate email is detected, RAISE_APPLICATION_ERROR is invoked, immediately stopping the procedure and returning a custom error code (-20002) and message to the caller. This is a standard and effective method for propagating clear, application-specific error conditions from your Oracle database procedures back to client applications, enabling more sophisticated error handling and user feedback.
Example 3: RAISE_APPLICATION_ERROR from an Exception Handler
DECLARE
v_dept_name VARCHAR2(100);
BEGIN
BEGIN
-- Simulate trying to fetch a department that doesn't exist
SELECT department_name INTO v_dept_name FROM departments WHERE department_id = 999; -- Assuming 'departments' table
EXCEPTION
WHEN NO_DATA_FOUND THEN
-- Instead of just handling, translate into a meaningful application error
RAISE_APPLICATION_ERROR(-20003, 'Requested department not found. Please verify department ID.');
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20004, 'An unexpected database error occurred: ' || SQLERRM);
END;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Outer block caught error: ' || SQLCODE || ' - ' || SQLERRM);
END;
/
Explanation
This example shows RAISE_APPLICATION_ERROR being called from within an EXCEPTION handler. When NO_DATA_FOUND occurs from the SELECT statement, instead of simply handling it internally, the code translates this standard Oracle error into a more user-friendly, application-specific error message using RAISE_APPLICATION_ERROR(-20003, ...). This re-raises the error with a custom code and message, which is then caught by the outer WHEN OTHERS block. This technique is invaluable for abstracting technical database errors into business-meaningful messages, providing cleaner error reporting in Oracle PL/SQL.
Example 4: Using RAISE_APPLICATION_ERROR in a Function
CREATE OR REPLACE FUNCTION get_employee_salary (
p_employee_id IN NUMBER
) RETURN NUMBER
IS
v_salary NUMBER;
BEGIN
SELECT salary INTO v_salary
FROM employees
WHERE employee_id = p_employee_id; -- Assuming 'employees' table with 'salary' column
RETURN v_salary;
EXCEPTION
WHEN NO_DATA_FOUND THEN
-- If employee not found, raise an application error
RAISE_APPLICATION_ERROR(-20005, 'Employee with ID ' || p_employee_id || ' does not exist.');
WHEN TOO_MANY_ROWS THEN
-- If somehow multiple employees found (data integrity issue), raise an application error
RAISE_APPLICATION_ERROR(-20006, 'Multiple employees found for ID ' || p_employee_id || '. Data integrity error.');
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20007, 'An unexpected error occurred fetching salary for employee ' || p_employee_id || ': ' || SQLERRM);
END;
/
-- Test cases
DECLARE
v_emp_salary NUMBER;
BEGIN
DBMS_OUTPUT.PUT_LINE('--- Test Case 1: Valid Employee ID ---');
-- Assuming employee_id 100 exists with salary 50000
-- IF NOT EXISTS (SELECT 1 FROM employees WHERE employee_id = 100) THEN
-- INSERT INTO employees (employee_id, first_name, salary) VALUES (100, 'John', 50000);
-- END IF;
-- v_emp_salary := get_employee_salary(100);
-- DBMS_OUTPUT.PUT_LINE('Salary for employee 100: ' || v_emp_salary);
DBMS_OUTPUT.PUT_LINE('--- Test Case 2: Non-existent Employee ID ---');
BEGIN
v_emp_salary := get_employee_salary(99999);
DBMS_OUTPUT.PUT_LINE('Salary for employee 99999: ' || v_emp_salary);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Caught error: ' || SQLCODE || ' - ' || SQLERRM);
END;
DBMS_OUTPUT.PUT_LINE('--- Test Case 3: Hypothetical Too_Many_Rows (data issue) ---');
-- To test TOO_MANY_ROWS, you'd need to create a scenario where employee_id is not unique
-- For demonstration, this path won't be easily triggered without data manipulation.
-- Consider a scenario where a non-unique index exists on employee_id temporarily.
END;
/
Explanation
This function get_employee_salary uses RAISE_APPLICATION_ERROR to handle specific scenarios where fetching an employee's salary might go wrong. Instead of letting NO_DATA_FOUND or TOO_MANY_ROWS propagate as generic Oracle errors, the function catches them and re-raises them with custom error numbers and messages, tailored for the application. This ensures that callers of the function receive clear, actionable error feedback, making your Oracle PL/SQL functions more user-friendly and simplifying error interpretation in client applications.
Example 5: Combining RAISE_APPLICATION_ERROR with SQLCODE and SQLERRM
CREATE TABLE product_categories (
category_id NUMBER PRIMARY KEY,
category_name VARCHAR2(100) UNIQUE
);
-- Insert some sample data (if not already existing)
-- INSERT INTO product_categories (category_id, category_name) VALUES (1, 'Electronics');
-- INSERT INTO product_categories (category_id, category_name) VALUES (2, 'Books');
-- COMMIT;
DECLARE
v_category_id NUMBER := 3;
v_category_name VARCHAR2(100) := 'Electronics'; -- Will cause unique constraint violation
BEGIN
INSERT INTO product_categories (category_id, category_name)
VALUES (v_category_id, v_category_name);
DBMS_OUTPUT.PUT_LINE('Category ' || v_category_name || ' inserted successfully.');
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE = -1 THEN -- ORA-00001: unique constraint violated
RAISE_APPLICATION_ERROR(-20008, 'Category name "' || v_category_name || '" already exists. Please choose a different name.');
ELSIF SQLCODE = -2291 THEN -- ORA-02291: integrity constraint violated - parent key not found
RAISE_APPLICATION_ERROR(-20009, 'Parent category not found for ID ' || v_category_id || '. Original error: ' || SQLERRM);
ELSE
-- For any other unhandled error, provide generic details but keep custom code
RAISE_APPLICATION_ERROR(-20010, 'An unhandled database error occurred: ' || SQLERRM);
END IF;
END;
/
-- Cleanup (optional)
-- DROP TABLE product_categories;
Explanation
This example demonstrates a more sophisticated use of RAISE_APPLICATION_ERROR by combining it with SQLCODE and SQLERRM. Inside the EXCEPTION block, specific Oracle errors (like ORA-00001 for unique constraint violation) are identified using SQLCODE. For these known errors, a custom, highly specific application error message is constructed using RAISE_APPLICATION_ERROR, incorporating details from the original SQLERRM where appropriate. This provides exceptionally precise and user-friendly error reporting, which is a hallmark of well-designed Oracle PL/SQL applications, making debugging easier and improving the user experience for database interactions.
Exception Propagation
Exception propagation is the mechanism by which an unhandled exception in a PL/SQL block or subprogram moves up the call stack to the enclosing block or calling environment. When an exception occurs, PL/SQL first searches for an exception handler in the current block's EXCEPTION section. If no specific handler is found, or if a RAISE; statement is used, the exception propagates to the next enclosing block. This process continues until a suitable handler is found or until the exception reaches the outermost PL/SQL environment (e.g., SQL*Plus, SQL Developer, or a client application), at which point it becomes an unhandled exception, causing the program to terminate. Understanding exception propagation is crucial for designing robust and predictable Oracle PL/SQL error handling strategies.
Example 1: Exception Handled in the Same Block
DECLARE
v_num1 NUMBER := 10;
v_num2 NUMBER := 0;
v_result NUMBER;
BEGIN
-- This operation will cause a ZERO_DIVIDE exception
v_result := v_num1 / v_num2;
DBMS_OUTPUT.PUT_LINE('Result: ' || v_result); -- This line will not be executed
EXCEPTION
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE('Error: Cannot divide by zero. Result set to NULL.');
v_result := NULL; -- Handle the error and continue program flow
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An unexpected error occurred: ' || SQLERRM);
END;
/
Explanation
This example shows a ZERO_DIVIDE exception that occurs and is handled within the same PL/SQL block where it originates. The EXCEPTION section directly catches ZERO_DIVIDE, prints an informative message, and sets v_result to NULL. Because the exception is fully handled within the block, it does not propagate further, and the program completes gracefully. This is the simplest form of exception handling, ensuring local recovery and preventing program termination due to common errors in Oracle PL/SQL.
Example 2: Exception Propagating from Inner to Outer Block
DECLARE
v_message VARCHAR2(100);
BEGIN
DBMS_OUTPUT.PUT_LINE('Outer block: Starting execution.');
BEGIN -- Inner block starts
DBMS_OUTPUT.PUT_LINE('Inner block: Starting execution.');
-- This SELECT INTO will raise NO_DATA_FOUND
SELECT 'Some Data' INTO v_message FROM DUAL WHERE 1 = 2;
DBMS_OUTPUT.PUT_LINE('Inner block: Data fetched: ' || v_message); -- This line won't be reached
EXCEPTION
-- No specific handler for NO_DATA_FOUND in inner block
WHEN VALUE_ERROR THEN
DBMS_OUTPUT.PUT_LINE('Inner block: Caught VALUE_ERROR, will not propagate NO_DATA_FOUND.');
-- If no handler, or an explicit RAISE; the exception propagates
-- If we don't have a specific handler for NO_DATA_FOUND, it will propagate.
END; -- Inner block ends
DBMS_OUTPUT.PUT_LINE('Outer block: Inner block finished (this line reached if inner block handles or no error).'); -- This line will not be reached
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Outer block: Caught NO_DATA_FOUND propagated from inner block.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Outer block: Caught an unexpected error: ' || SQLERRM);
END;
/
Explanation
This example demonstrates an exception (NO_DATA_FOUND) propagating from an inner anonymous block to its enclosing outer block. The SELECT INTO in the inner block fails, raising NO_DATA_FOUND. Since the inner block does not have a specific WHEN NO_DATA_FOUND handler (it only has a VALUE_ERROR handler as a decoy), the exception is not caught locally and automatically propagates to the outer block. The outer block then successfully catches and handles NO_DATA_FOUND. This illustrates the default exception propagation behavior in Oracle PL/SQL, a key concept for understanding error flow in nested program units.
Example 3: Exception Propagating from a Procedure to Calling Block
CREATE OR REPLACE PROCEDURE fetch_customer_email (
p_customer_id IN NUMBER,
p_customer_email OUT VARCHAR2
)
IS
BEGIN
SELECT email_address
INTO p_customer_email
FROM customers
WHERE customer_id = p_customer_id; -- Assuming 'customers' table and email_address column
EXCEPTION
-- Procedure does not handle NO_DATA_FOUND specifically
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Procedure: An error occurred inside fetch_customer_email: ' || SQLERRM);
RAISE; -- Re-raise to propagate the original exception
END;
/
DECLARE
v_email VARCHAR2(100);
BEGIN
DBMS_OUTPUT.PUT_LINE('Calling block: Attempting to fetch email for non-existent customer.');
fetch_customer_email(99999, v_email); -- Call procedure with non-existent ID
DBMS_OUTPUT.PUT_LINE('Calling block: Email fetched: ' || v_email); -- This line won't be reached
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Calling block: Caught NO_DATA_FOUND propagated from procedure.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Calling block: Caught unexpected error: ' || SQLERRM);
END;
/
Explanation
Here, an exception (NO_DATA_FOUND) originates within a stored procedure fetch_customer_email. The procedure's EXCEPTION block catches OTHERS and re-raises the original exception using RAISE;. This causes NO_DATA_FOUND to propagate out of the procedure to the anonymous calling block. The calling block then catches and handles this propagated exception. This demonstrates a common pattern where subprograms might log an error but then allow a higher-level calling program to decide on recovery or further handling, a crucial aspect of distributed error management in Oracle PL/SQL applications.
Example 4: Exception Propagating from a Function to Calling Block
CREATE OR REPLACE FUNCTION get_product_name (
p_product_id IN NUMBER
) RETURN VARCHAR2
IS
v_product_name VARCHAR2(100);
BEGIN
SELECT product_name
INTO v_product_name
FROM products
WHERE product_id = p_product_id; -- Assuming 'products' table
RETURN v_product_name;
EXCEPTION
-- Function does not specifically handle NO_DATA_FOUND or others, it lets them propagate
-- Alternatively, could re-raise with RAISE; or RAISE_APPLICATION_ERROR
NULL; -- This 'NULL' effectively means no local handler, so it propagates
END;
/
DECLARE
v_name VARCHAR2(100);
BEGIN
DBMS_OUTPUT.PUT_LINE('Calling block: Attempting to get product name.');
v_name := get_product_name(99999); -- Call function with non-existent ID
DBMS_OUTPUT.PUT_LINE('Calling block: Product name: ' || v_name); -- This line won't be reached
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Calling block: Caught NO_DATA_FOUND propagated from function.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Calling block: Caught unexpected error: ' || SQLERRM);
END;
/
Explanation
This example illustrates exception propagation from a PL/SQL function. The get_product_name function attempts to retrieve a product name for a non-existent ID, which triggers NO_DATA_FOUND. Crucially, the function's EXCEPTION section contains NULL;, meaning it has no explicit handler for NO_DATA_FOUND or OTHERS. As a result, the NO_DATA_FOUND exception automatically propagates out of the function to the anonymous calling block, where it is successfully caught and handled. This demonstrates the default propagation behavior when a subprogram does not fully handle an exception, a fundamental concept for understanding error flow in Oracle PL/SQL development.
Example 5: Demonstrating Unhandled Exception Propagation to the Environment
-- Outer PL/SQL Block
DECLARE
v_value NUMBER;
BEGIN
DBMS_OUTPUT.PUT_LINE('Outer block: Starting execution.');
BEGIN -- Inner block
DBMS_OUTPUT.PUT_LINE('Inner block: Starting execution.');
v_value := 10 / 0; -- Will cause ZERO_DIVIDE
DBMS_OUTPUT.PUT_LINE('Inner block: Value: ' || v_value); -- Not reached
EXCEPTION
-- This handler catches a different exception, so ZERO_DIVIDE is unhandled here
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Inner block: Caught NO_DATA_FOUND.');
END; -- End of inner block, ZERO_DIVIDE is unhandled and propagates
DBMS_OUTPUT.PUT_LINE('Outer block: Inner block finished (this will not be reached).');
EXCEPTION
-- This handler catches a different exception, so ZERO_DIVIDE is unhandled here too
WHEN CURSOR_ALREADY_OPEN THEN
DBMS_OUTPUT.PUT_LINE('Outer block: Caught CURSOR_ALREADY_OPEN.');
-- If no WHEN OTHERS, ZERO_DIVIDE will propagate out of this block to SQL*Plus/SQL Developer
END;
/
Explanation
This example demonstrates an unhandled exception propagating all the way out of the PL/SQL execution environment. A ZERO_DIVIDE exception occurs in the inner block. Neither the inner nor the outer block has a handler for ZERO_DIVIDE (they have handlers for other specific exceptions but not this one, and no WHEN OTHERS). Therefore, the ZERO_DIVIDE exception propagates completely out of the PL/SQL code. In an environment like SQL*Plus or SQL Developer, this would result in an "ORA-01476: divisor is equal to zero" error being displayed to the user, causing the entire script to stop. This highlights the importance of comprehensive exception handling, especially the use of WHEN OTHERS, to prevent unhandled exceptions from crashing your Oracle PL/SQL programs and providing cryptic error messages to users.
The PRAGMA EXCEPTION_INIT Directive
The PRAGMA EXCEPTION_INIT directive is a compiler directive in Oracle PL/SQL that allows you to associate a specific Oracle error number with a user-defined exception name. This is particularly useful for handling standard Oracle errors that do not have predefined exception names (e.g., ORA-00001 for unique constraint violation, which doesn't have a named exception like NO_DATA_FOUND). By using PRAGMA EXCEPTION_INIT, you can create meaningful, readable exception handlers for these specific errors, leading to more robust and maintainable Oracle PL/SQL code. The error number must be a negative integer, typically an ORA- error number.
Example 1: Associating with ORA-00001 (Unique Constraint Violation)
DECLARE
-- Declare a user-defined exception
e_duplicate_entry EXCEPTION;
-- Associate the user-defined exception with ORA-00001 (unique constraint violated)
PRAGMA EXCEPTION_INIT(e_duplicate_entry, -00001);
v_category_id NUMBER := 10;
v_category_name VARCHAR2(100) := 'Electronics';
BEGIN
-- Create a dummy table for demonstration (if it doesn't exist)
EXECUTE IMMEDIATE 'CREATE TABLE temp_categories (id NUMBER PRIMARY KEY, name VARCHAR2(100) UNIQUE)';
-- Ensure cleanup later: DROP TABLE temp_categories;
-- First insert should succeed
INSERT INTO temp_categories (id, name) VALUES (1, 'Books');
DBMS_OUTPUT.PUT_LINE('Inserted Books.');
-- This insert will cause ORA-00001 because 'Electronics' is already in use
INSERT INTO temp_categories (id, name) VALUES (2, v_category_name); -- Assuming 'Electronics' already exists
-- Or, if we try to insert duplicate 'name' after previous run
-- INSERT INTO temp_categories (id, name) VALUES (1, 'Movies'); -- To cause ORA-00001 on ID
-- Let's try to insert the same name, assuming it's already there from a previous run or an existing row
INSERT INTO temp_categories (id, name) VALUES (v_category_id, v_category_name);
DBMS_OUTPUT.PUT_LINE('Inserted ' || v_category_name || ' with ID ' || v_category_id || '.');
EXCEPTION
WHEN e_duplicate_entry THEN -- Now we can catch ORA-00001 by its friendly name
DBMS_OUTPUT.PUT_LINE('Error: Duplicate category name or ID encountered. Please use a unique value.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An unexpected error occurred: ' || SQLCODE || ' - ' || SQLERRM);
END;
/
-- Clean up (run separately if needed)
-- DROP TABLE temp_categories;
Explanation
This classic example demonstrates PRAGMA EXCEPTION_INIT used to associate the user-defined exception e_duplicate_entry with the Oracle error ORA-00001 (unique constraint violation). When the INSERT statement attempts to violate the UNIQUE constraint on the name column (or PRIMARY KEY on id), it raises ORA-00001. Because of PRAGMA EXCEPTION_INIT, the EXCEPTION handler can catch this specific error using the more readable WHEN e_duplicate_entry THEN instead of WHEN OTHERS THEN IF SQLCODE = -1. This significantly improves code readability and maintainability for common database errors in Oracle PL/SQL.
Example 2: Handling a Specific DML Error (ORA-01400: cannot insert NULL into (...))
DECLARE
e_null_column_error EXCEPTION;
-- Associate with ORA-01400 (cannot insert NULL into NOT NULL column)
PRAGMA EXCEPTION_INIT(e_null_column_error, -01400);
BEGIN
-- Create a dummy table with a NOT NULL column
EXECUTE IMMEDIATE 'CREATE TABLE temp_products (product_id NUMBER PRIMARY KEY, product_name VARCHAR2(100) NOT NULL)';
-- Ensure cleanup later: DROP TABLE temp_products;
-- This insert will cause ORA-01400 because product_name is NULL
INSERT INTO temp_products (product_id, product_name) VALUES (1, NULL);
DBMS_OUTPUT.PUT_LINE('Product inserted successfully.');
EXCEPTION
WHEN e_null_column_error THEN -- Catch ORA-01400 by its named exception
DBMS_OUTPUT.PUT_LINE('Error: Attempted to insert NULL into a required column (product_name).');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An unexpected error occurred: ' || SQLCODE || ' - ' || SQLERRM);
END;
/
-- Clean up (run separately if needed)
-- DROP TABLE temp_products;
Explanation
This example demonstrates PRAGMA EXCEPTION_INIT for handling ORA-01400, which occurs when trying to insert NULL into a NOT NULL column. By associating e_null_column_error with -01400, the EXCEPTION block can specifically target this common DML error with a named handler. This makes the code more explicit and readable than relying on SQLCODE within a WHEN OTHERS block, promoting clearer and more efficient error management in Oracle PL/SQL applications.
Example 3: Using PRAGMA EXCEPTION_INIT for a Network-Related Error (e.g., ORA-03113: end-of-file on communication channel)
DECLARE
e_network_disconnect EXCEPTION;
-- Associate with ORA-03113 (example of a common network error)
PRAGMA EXCEPTION_INIT(e_network_disconnect, -03113);
BEGIN
-- Simulate a database link operation that might fail due to network issues
-- This is conceptual, as triggering ORA-03113 intentionally is hard in a simple script.
-- In a real scenario, this might be a SELECT from a dblink, or a call to a remote procedure.
-- For demonstration, we'll just raise it.
DBMS_OUTPUT.PUT_LINE('Attempting a database operation that might experience network issues...');
-- Simulate an operation that could cause ORA-03113.
-- To actually trigger this, you might need to simulate network disconnection
-- or an issue with a database link target.
-- For the purpose of this example, we'll explicitly raise the error.
-- This is for demonstration only; real ORA-03113 comes from system.
-- RAISE_APPLICATION_ERROR(-3113, 'Simulated ORA-03113: end-of-file on communication channel.'); -- Cannot raise ORA-03113 directly
-- To simulate the error for PRAGMA EXCEPTION_INIT testing:
-- You would typically get this from a real network problem.
-- For academic purposes, let's assume an operation internally generates this.
-- A simple way to simulate it for testing is to force the error number to occur.
-- In practice, you'd typically react to a real ORA-03113 thrown by Oracle.
-- Let's just catch it as if it happened.
RAISE e_network_disconnect;
EXCEPTION
WHEN e_network_disconnect THEN
DBMS_OUTPUT.PUT_LINE('Error: Database connection lost or network issue (ORA-03113). Please check network connectivity and database link status.');
-- Log the error, attempt reconnect, or notify administrator
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An unexpected error occurred: ' || SQLCODE || ' - ' || SQLERRM);
END;
/
Explanation
This example illustrates using PRAGMA EXCEPTION_INIT to give a named exception (e_network_disconnect) to a system-level network error like ORA-03113 (end-of-file on communication channel). While directly simulating ORA-03113 is challenging, the concept is to enable specific handling for such critical infrastructure errors. By naming the exception, your PL/SQL code can react decisively to network disconnections or similar issues, allowing for custom logging, retry mechanisms, or user alerts, thereby enhancing the resilience of database applications that rely on external connections.
Example 4: Defining Multiple PRAGMA EXCEPTION_INIT Exceptions in One Block
DECLARE
e_unique_violation EXCEPTION;
PRAGMA EXCEPTION_INIT(e_unique_violation, -00001); -- ORA-00001
e_not_null_violation EXCEPTION;
PRAGMA EXCEPTION_INIT(e_not_null_violation, -01400); -- ORA-01400
e_foreign_key_violation EXCEPTION;
PRAGMA EXCEPTION_INIT(e_foreign_key_violation, -02291); -- ORA-02291 (integrity constraint violated - parent key not found)
v_employee_id NUMBER := 10;
v_department_id NUMBER := 999; -- A department ID unlikely to exist
BEGIN
-- Create dummy tables for demonstration
EXECUTE IMMEDIATE 'CREATE TABLE employees_test (employee_id NUMBER PRIMARY KEY, department_id NUMBER, employee_name VARCHAR2(100), CONSTRAINT fk_dept FOREIGN KEY (department_id) REFERENCES departments_test(department_id))';
EXECUTE IMMEDIATE 'CREATE TABLE departments_test (department_id NUMBER PRIMARY KEY, department_name VARCHAR2(100) UNIQUE)';
-- Add some data to departments_test for foreign key to work sometimes
INSERT INTO departments_test VALUES (10, 'IT');
COMMIT;
-- Scenario 1: Attempt to insert with non-existent foreign key (ORA-02291)
INSERT INTO employees_test (employee_id, department_id, employee_name) VALUES (v_employee_id, v_department_id, 'John Doe');
DBMS_OUTPUT.PUT_LINE('Employee inserted successfully (this will not be reached).');
EXCEPTION
WHEN e_unique_violation THEN
DBMS_OUTPUT.PUT_LINE('Handled: Unique constraint violated.');
WHEN e_not_null_violation THEN
DBMS_OUTPUT.PUT_LINE('Handled: NOT NULL constraint violated.');
WHEN e_foreign_key_violation THEN
DBMS_OUTPUT.PUT_LINE('Handled: Foreign key constraint violated. Department ID ' || v_department_id || ' does not exist.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An unexpected general error occurred: ' || SQLCODE || ' - ' || SQLERRM);
END;
/
-- Cleanup (run separately if needed)
-- DROP TABLE employees_test;
-- DROP TABLE departments_test;
Explanation
This example demonstrates the power of defining multiple PRAGMA EXCEPTION_INIT directives within a single PL/SQL block. It sets up named exceptions for unique constraint violations (ORA-00001), NOT NULL violations (ORA-01400), and foreign key violations (ORA-02291). The INSERT statement is designed to trigger a foreign key violation. The EXCEPTION section then catches this specific error using its assigned named exception, e_foreign_key_violation, providing a clear and precise error message. This pattern is essential for comprehensive and explicit handling of various common database errors, enhancing the robustness and clarity of your Oracle PL/SQL code.
Example 5: Applying PRAGMA EXCEPTION_INIT within a Stored Procedure
CREATE OR REPLACE PROCEDURE update_employee_department (
p_employee_id IN NUMBER,
p_new_department_id IN NUMBER
)
IS
e_parent_key_not_found EXCEPTION;
PRAGMA EXCEPTION_INIT(e_parent_key_not_found, -02291); -- ORA-02291 (parent key not found - FK violation)
e_unique_violation EXCEPTION;
PRAGMA EXCEPTION_INIT(e_unique_violation, -00001); -- ORA-00001 (unique constraint violation)
BEGIN
-- Create dummy tables for demonstration (if not already existing from previous example)
-- This assumes employees_test and departments_test are created and have data.
-- INSERT into employees_test (employee_id, employee_name, department_id) VALUES (1, 'Alice', 10);
-- INSERT into departments_test (department_id, department_name) VALUES (10, 'HR');
-- COMMIT;
-- Attempt to update an employee's department
UPDATE employees_test
SET department_id = p_new_department_id
WHERE employee_id = p_employee_id;
IF SQL%ROWCOUNT = 0 THEN
RAISE_APPLICATION_ERROR(-20011, 'Employee ' || p_employee_id || ' not found.');
END IF;
DBMS_OUTPUT.PUT_LINE('Employee ' || p_employee_id || ' department updated to ' || p_new_department_id || '.');
EXCEPTION
WHEN e_parent_key_not_found THEN
DBMS_OUTPUT.PUT_LINE('Error: Department ID ' || p_new_department_id || ' does not exist. Update failed.');
WHEN e_unique_violation THEN
DBMS_OUTPUT.PUT_LINE('Error: Unique constraint violated during update. Check for duplicate data.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An unexpected error occurred during update: ' || SQLCODE || ' - ' || SQLERRM);
END;
/
-- Test Cases
BEGIN
-- Assuming employee 1 exists and department 10 exists
-- And department 999 does not exist
DBMS_OUTPUT.PUT_LINE('--- Test Case 1: Valid Update (Assuming employee 1, dept 10 exists) ---');
-- update_employee_department(1, 10);
DBMS_OUTPUT.PUT_LINE('--- Test Case 2: Invalid Department ID (ORA-02291) ---');
update_employee_department(1, 999); -- Assuming employee 1 exists, but dept 999 does not
DBMS_OUTPUT.PUT_LINE('--- Test Case 3: Employee Not Found ---');
update_employee_department(9999, 10); -- Assuming employee 9999 does not exist
END;
/
Explanation
This example demonstrates the practical application of PRAGMA EXCEPTION_INIT within a stored procedure, update_employee_department. It defines named exceptions for foreign key violations (e_parent_key_not_found for ORA-02291) and unique constraint violations (e_unique_violation for ORA-00001). If an attempt is made to assign an employee to a non-existent department, e_parent_key_not_found is raised and caught, providing a clear error message specific to the data integrity issue. This pattern is crucial for writing robust and professional Oracle PL/SQL stored procedures, allowing for precise handling of database constraints and improving the overall stability of your database applications.