Oracle PL SQL Tutorial day 12
Overview:
Exception handling in PL/SQL allows developers to manage errors and maintain control over application behavior during unexpected runtime conditions. This blog dives deep into exception types, usage, best practices, and hands-on examples using the HR schema.
1. What Are Exceptions?
Exceptions are runtime errors that interrupt normal program flow. PL/SQL allows handling these gracefully using the EXCEPTION block.
2. PL/SQL Block Structure with Exceptions:
DECLARE
-- variable declarations
BEGIN
-- executable statements
EXCEPTION
WHEN exception_name THEN
-- handling logic
END;
3. Types of Exceptions:
1. Predefined (built-in): e.g., NO_DATA_FOUND, TOO_MANY_ROWS, ZERO_DIVIDE
2. Non-predefined (system exceptions, declared manually)
3. User-defined (custom logic)
4. Example 1: NO_DATA_FOUND
DECLARE
v_name employees.first_name%TYPE;
BEGIN
SELECT first_name INTO v_name
FROM employees
WHERE employee_id = 9999;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No employee found.');
END;
5. Common Predefined Exceptions:
Exception NO_DATA_FOUND
code 01403
description No rows found
Exception TOO_MANY_ROWS
code 01422
description More than one row found
Exception ZERO_DIVIDE
code 01476
description Division by zero
Exception INVALID_CURSOR
code 01001
Description Invalid cursor usage
There are more exceptions you may visit oracle documentation for details
You may add When_others exception to control un known errors.
6. Example 2: TOO_MANY_ROWS
DECLARE
v_name employees.first_name%TYPE;
BEGIN
SELECT first_name INTO v_name
FROM employees
WHERE department_id = 60;
EXCEPTION
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('More than one result.');
END;
7. Example 3: ZERO_DIVIDE
DECLARE
v_result NUMBER;
BEGIN
v_result := 100 / 0;
EXCEPTION
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE('Division by zero error.');
END;
8. User-Defined Exceptions:
DECLARE
v_salary employees.salary%TYPE;
e_low_salary EXCEPTION;
BEGIN
SELECT salary INTO v_salary
FROM employees
WHERE employee_id = 101;
IF v_salary < 3000 THEN
RAISE e_low_salary;
END IF;
EXCEPTION
WHEN e_low_salary THEN
DBMS_OUTPUT.PUT_LINE('Salary too low.');
END;
9. Using PRAGMA EXCEPTION_INIT:
DECLARE
e_dup EXCEPTION;
PRAGMA EXCEPTION_INIT(e_dup, -1);
BEGIN
INSERT INTO departments(department_id, department_name)
VALUES(10, 'HR');
EXCEPTION
WHEN e_dup THEN
DBMS_OUTPUT.PUT_LINE('Duplicate entry.');
END;
10. WHEN OTHERS Handler:
BEGIN
-- some operation
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
11. SQLERRM and SQLCODE:
BEGIN
RAISE_APPLICATION_ERROR(-20001, 'Manual exception');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Code: ' || SQLCODE);
DBMS_OUTPUT.PUT_LINE('Message: ' || SQLERRM);
END;
12. Re-Raising Exceptions:
BEGIN
BEGIN
-- inner logic
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE;
END;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Handled in outer block.');
END;
13. Nested Blocks:
BEGIN
BEGIN
SELECT salary INTO :v_sal FROM employees WHERE employee_id = 9999;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Inner block error.');
END;
DBMS_OUTPUT.PUT_LINE('Continued in outer block.');
END;
14. RAISE_APPLICATION_ERROR:
DECLARE
v_salary employees.salary%TYPE;
BEGIN
SELECT salary INTO v_salary FROM employees WHERE employee_id = 100;
IF v_salary > 20000 THEN
RAISE_APPLICATION_ERROR(-20010, 'Salary too high.');
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
15. Best Practices:
- Always handle specific exceptions.
- Use WHEN OTHERS carefully.
- Re-raise errors only after logging.
- Use application-specific exceptions for business rules.
- Log exceptions to a table if needed.
16. Example: Logging to Custom Error Table
CREATE TABLE error_log (
id NUMBER GENERATED ALWAYS AS IDENTITY,
message VARCHAR2(4000),
log_date DATE DEFAULT SYSDATE
);
BEGIN
EXECUTE IMMEDIATE 'SELECT * FROM non_existing_table';
EXCEPTION
WHEN OTHERS THEN
INSERT INTO error_log(message) VALUES(SQLERRM);
END;
17. Procedure with Exception Handling:
CREATE OR REPLACE PROCEDURE show_salary(p_emp_id IN NUMBER) IS
v_sal employees.salary%TYPE;
BEGIN
SELECT salary INTO v_sal FROM employees WHERE employee_id = p_emp_id;
DBMS_OUTPUT.PUT_LINE('Salary: ' || v_sal);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Employee not found.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Unexpected error: ' || SQLERRM);
END;
18. Summary of Exception Handling Workflow:
- - Check business rules.
- - Use SELECT INTO carefully.
- - Catch predefined exceptions.
- - Use PRAGMA or custom errors.
- - Log errors consistently.
- - Handle unexpected issues with WHEN OTHERS.
19. Conclusion:
All example showed as anonymous block but it can be use in procedure, function and packages as well.
Exception handling is a vital part of robust PL/SQL development. Whether it's handling missing rows, constraint violations, or application logic errors, PL/SQL provides mechanisms to handle them effectively. Following best practices and writing clean exception blocks ensures that your code is maintainable and reliable in production environments.
0 Comments