Exception Handling in Oracle Database
Key Concepts
1. Exception Handling Overview
Exception handling in Oracle Database allows you to manage errors and unexpected conditions that occur during the execution of PL/SQL blocks. It ensures that your code can gracefully recover from errors and continue execution.
Example:
DECLARE
v_name VARCHAR2(50);
BEGIN
SELECT name INTO v_name FROM employees WHERE id = 100;
DBMS_OUTPUT.PUT_LINE(v_name);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No employee found');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An error occurred');
END;
2. Predefined Exceptions
Oracle provides a set of predefined exceptions that cover common error conditions. These exceptions are automatically raised by the database when specific errors occur.
Example:
DECLARE
v_name VARCHAR2(50);
BEGIN
SELECT name INTO v_name FROM employees WHERE id = 100;
DBMS_OUTPUT.PUT_LINE(v_name);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No employee found');
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('Multiple rows found');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An error occurred');
END;
3. User-Defined Exceptions
User-defined exceptions allow you to create custom error conditions that are specific to your application. These exceptions can be raised and handled within your PL/SQL code.
Example:
DECLARE
e_invalid_salary EXCEPTION;
v_salary NUMBER := -1000;
BEGIN
IF v_salary < 0 THEN
RAISE e_invalid_salary;
END IF;
EXCEPTION
WHEN e_invalid_salary THEN
DBMS_OUTPUT.PUT_LINE('Invalid salary');
END;
4. RAISE Statement
The RAISE statement is used to explicitly raise an exception. It can be used to raise both predefined and user-defined exceptions.
Example:
DECLARE
e_invalid_salary EXCEPTION;
v_salary NUMBER := -1000;
BEGIN
IF v_salary < 0 THEN
RAISE e_invalid_salary;
END IF;
EXCEPTION
WHEN e_invalid_salary THEN
DBMS_OUTPUT.PUT_LINE('Invalid salary');
END;
5. RAISE_APPLICATION_ERROR Procedure
The RAISE_APPLICATION_ERROR procedure allows you to raise a user-defined error and specify a custom error message. This is useful for providing more informative error messages to the user.
Example:
DECLARE
v_salary NUMBER := -1000;
BEGIN
IF v_salary < 0 THEN
RAISE_APPLICATION_ERROR(-20001, 'Salary cannot be negative');
END IF;
END;
6. EXCEPTION_INIT Pragma
The EXCEPTION_INIT pragma allows you to associate a user-defined exception with a specific Oracle error code. This enables you to handle specific Oracle errors using your custom exception.
Example:
DECLARE
e_integrity_violation EXCEPTION;
PRAGMA EXCEPTION_INIT(e_integrity_violation, -2291);
BEGIN
-- Code that may raise an integrity constraint violation
EXCEPTION
WHEN e_integrity_violation THEN
DBMS_OUTPUT.PUT_LINE('Integrity constraint violated');
END;
7. OTHERS Exception Handler
The OTHERS exception handler is a catch-all handler that can be used to handle any exception that is not explicitly handled by other exception handlers. It is typically placed at the end of the EXCEPTION section.
Example:
DECLARE
v_name VARCHAR2(50);
BEGIN
SELECT name INTO v_name FROM employees WHERE id = 100;
DBMS_OUTPUT.PUT_LINE(v_name);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No employee found');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An error occurred');
END;
8. SQLCODE and SQLERRM
SQLCODE and SQLERRM are built-in functions that return the error code and error message associated with the last exception that occurred. They are useful for logging and debugging purposes.
Example:
DECLARE
v_name VARCHAR2(50);
BEGIN
SELECT name INTO v_name FROM employees WHERE id = 100;
DBMS_OUTPUT.PUT_LINE(v_name);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error Code: ' || SQLCODE);
DBMS_OUTPUT.PUT_LINE('Error Message: ' || SQLERRM);
END;
9. Exception Propagation
Exceptions that are not handled within a PL/SQL block are propagated to the calling environment. This allows for hierarchical error handling, where errors can be caught and handled at different levels of the call stack.
Example:
DECLARE
v_name VARCHAR2(50);
BEGIN
BEGIN
SELECT name INTO v_name FROM employees WHERE id = 100;
DBMS_OUTPUT.PUT_LINE(v_name);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No employee found');
END;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An error occurred');
END;
10. Exception Handling in Procedures and Functions
Exception handling in procedures and functions follows the same principles as in anonymous PL/SQL blocks. Exceptions can be raised, caught, and handled within the procedure or function, or they can be propagated to the calling environment.
Example:
CREATE OR REPLACE PROCEDURE get_employee_name(p_id IN NUMBER) IS
v_name VARCHAR2(50);
BEGIN
SELECT name INTO v_name FROM employees WHERE id = p_id;
DBMS_OUTPUT.PUT_LINE(v_name);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No employee found');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An error occurred');
END;
/
BEGIN
get_employee_name(100);
END;
11. Exception Handling in Triggers
Exception handling in triggers is similar to that in PL/SQL blocks. However, triggers have additional considerations, such as the ability to rollback changes and the need to handle specific trigger-related errors.
Example:
CREATE OR REPLACE TRIGGER before_insert_employee
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
IF :NEW.salary < 0 THEN
RAISE_APPLICATION_ERROR(-20001, 'Salary cannot be negative');
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An error occurred');
END;
/
12. Exception Handling in Dynamic SQL
Exception handling in dynamic SQL involves executing SQL statements dynamically and handling any exceptions that may occur during execution. The EXECUTE IMMEDIATE statement can be used to execute dynamic SQL, and exceptions can be caught and handled as needed.
Example:
DECLARE
v_sql VARCHAR2(100);
v_count NUMBER;
BEGIN
v_sql := 'SELECT COUNT(*) FROM employees';
EXECUTE IMMEDIATE v_sql INTO v_count;
DBMS_OUTPUT.PUT_LINE('Employee count: ' || v_count);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An error occurred');
END;
13. Exception Handling in Bulk Operations
Exception handling in bulk operations involves processing multiple rows at once and handling any exceptions that may occur during the operation. The BULK COLLECT and FORALL statements can be used for bulk operations, and exceptions can be caught and handled as needed.
Example:
DECLARE
TYPE t_ids IS TABLE OF employees.id%TYPE;
v_ids t_ids;
BEGIN
SELECT id BULK COLLECT INTO v_ids FROM employees;
FOR i IN 1..v_ids.COUNT LOOP
DBMS_OUTPUT.PUT_LINE('ID: ' || v_ids(i));
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An error occurred');
END;
14. Exception Handling in Pipelined Functions
Exception handling in pipelined functions involves returning a set of values one at a time and handling any exceptions that may occur during the operation. The PIPE ROW statement can be used to return values, and exceptions can be caught and handled as needed.
Example:
CREATE OR REPLACE FUNCTION get_employees RETURN SYS.ODCINUMBERLIST PIPELINED IS
BEGIN
FOR rec IN (SELECT id FROM employees) LOOP
PIPE ROW(rec.id);
END LOOP;
RETURN;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An error occurred');
END;
/
SELECT * FROM TABLE(get_employees);
15. Exception Handling in Object-Oriented PL/SQL
Exception handling in