In Oracle Database 23ai PL/SQL, errors can occur during code execution due to invalid data, missing rows, constraint violations, or runtime issues. Exception handling allows you to gracefully capture and manage these errors instead of letting your program crash. By using the EXCEPTION block, you can catch specific errors, handle them properly, and ensure your PL/SQL programs continue running smoothly.
In this Oracle tutorial, we will cover how to handle exceptions in Oracle 23ai, create custom exceptions, and apply them with modern features like JSON and vectors, along with sample data preparation to make the examples executable.
Preparing Sample Data
Before we begin, let’s create a sample table and insert some records that we can use to trigger exceptions.
-- Create table CREATE TABLE employees ( employee_id NUMBER PRIMARY KEY, first_name VARCHAR2(50), salary NUMBER ); -- Insert records INSERT INTO employees VALUES (101, 'Neena', 17000); INSERT INTO employees VALUES (102, 'Lex', 14000); INSERT INTO employees VALUES (103, 'Bruce', 9000); COMMIT;
We also prepare a small table for vector examples:
CREATE TABLE demo_vec (
id NUMBER PRIMARY KEY,
emb VECTOR(3)
);
INSERT INTO demo_vec VALUES (1, TO_VECTOR('[0.12,0.45,0.78]'));
INSERT INTO demo_vec VALUES (2, TO_VECTOR('[0.10,0.40,0.80]'));
COMMIT;
With this setup ready, we can explore exception handling.
Structure of Exception Handling in PL/SQL
A PL/SQL block can include an EXCEPTION section after the BEGIN...END block. The general structure is:
BEGIN
-- main logic
EXCEPTION
WHEN exception_name THEN
-- handle error
WHEN OTHERS THEN
-- handle unexpected errors
END;
You can handle specific exceptions or use WHEN OTHERS to catch all errors that are not explicitly defined.
Example 1: Handling NO_DATA_FOUND Exception
This example demonstrates what happens when a query returns no rows.
SET SERVEROUTPUT ON;
DECLARE
v_salary employees.salary%TYPE;
BEGIN
SELECT salary INTO v_salary
FROM employees
WHERE employee_id = 200; -- does not exist
DBMS_OUTPUT.PUT_LINE('Salary is ' || v_salary);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No employee found with the given ID');
END;
/
Result:
No employee found with the given ID
This shows how to gracefully handle the absence of data.
Example 2: Handling TOO_MANY_ROWS Exception
When a query returns more than one row, Oracle raises TOO_MANY_ROWS.
SET SERVEROUTPUT ON;
DECLARE
v_name employees.first_name%TYPE;
BEGIN
SELECT first_name INTO v_name
FROM employees; -- multiple rows
DBMS_OUTPUT.PUT_LINE('Employee: ' || v_name);
EXCEPTION
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('Query returned multiple employees, expected one');
END;
/
Result:
Query returned multiple employees, expected one
Example 3: Handling DUP_VAL_ON_INDEX Exception
This example demonstrates inserting a duplicate primary key.
SET SERVEROUTPUT ON;
BEGIN
INSERT INTO employees VALUES (101, 'Alice', 5000);
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
DBMS_OUTPUT.PUT_LINE('Duplicate Employee ID, cannot insert record');
END;
/
Result:
Duplicate Employee ID, cannot insert record
Example 4: WHEN OTHERS for Generic Handling
WHEN OTHERS catches any exception not explicitly handled.
SET SERVEROUTPUT ON;
BEGIN
UPDATE employees
SET salary = salary / 0; -- division by zero error
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error Occurred: ' || SQLERRM);
END;
/
Result (example):
Error Occurred: ORA-01476: divisor is equal to zero
Example 5: User-Defined Exception
You can declare your own exceptions and raise them using RAISE.
SET SERVEROUTPUT ON;
DECLARE
e_low_salary EXCEPTION;
v_salary employees.salary%TYPE := 3000;
BEGIN
IF v_salary < 5000 THEN
RAISE e_low_salary;
END IF;
DBMS_OUTPUT.PUT_LINE('Salary is acceptable');
EXCEPTION
WHEN e_low_salary THEN
DBMS_OUTPUT.PUT_LINE('Custom Exception: Salary is too low');
END;
/
Result:
Custom Exception: Salary is too low
Example 6: Exception Handling with JSON in Oracle 23ai
In this example, we handle errors while processing JSON data.
SET SERVEROUTPUT ON;
DECLARE
v_status VARCHAR2(50);
BEGIN
-- JSON_VALUE with ERROR ON ERROR raises an exception when the path is missing
SELECT JSON_VALUE('{"id":101,"name":"Alice"}',
'$.status' RETURNING VARCHAR2 ERROR ON ERROR)
INTO v_status
FROM dual;
DBMS_OUTPUT.PUT_LINE('Status: ' || v_status);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('JSON key not found: ' || SQLERRM);
END;
/
Result:
JSON key not found: ORA-40462: JSON path '$.status' evaluated to no value.
Example 7: Exception Handling with Vectors in Oracle 23ai
Vectors are new in Oracle 23ai, and errors can occur if dimensions mismatch. This example handles such an exception.
SET SERVEROUTPUT ON;
DECLARE
v_dist NUMBER;
BEGIN
SELECT VECTOR_DISTANCE(TO_VECTOR('[0.10,0.20,0.30]'),
TO_VECTOR('[0.10,0.20]')) -- mismatched size
INTO v_dist
FROM dual;
DBMS_OUTPUT.PUT_LINE('Distance: ' || v_dist);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Vector error: ' || SQLERRM);
END;
/
Result (example):
Vector error: ORA-40454: invalid vector dimension
Example 8: Logging Exceptions into a Table
A best practice is to log exceptions into a table for audit or debugging.
-- create log table
CREATE TABLE error_log (
err_time TIMESTAMP,
err_code NUMBER,
err_msg VARCHAR2(4000)
);
SET SERVEROUTPUT ON;
BEGIN
INSERT INTO employees VALUES (101, 'DuplicateTest', 7000);
EXCEPTION
WHEN OTHERS THEN
DECLARE
v_code NUMBER := SQLCODE;
v_msg VARCHAR2(4000) := SQLERRM;
BEGIN
INSERT INTO error_log (err_time, err_code, err_msg)
VALUES (SYSTIMESTAMP, v_code, v_msg);
COMMIT;
DBMS_OUTPUT.PUT_LINE('Error logged into error_log table');
END;
END;
/
Result:
Error logged into error_log table
Later, you can check:
SELECT * FROM error_log;
Common Mistakes in Exception Handling
- Overusing WHEN OTHERS – Catching all errors without re-raising them hides real issues
- Not logging exceptions – Makes debugging difficult in production
- Raising exceptions unnecessarily – Use exceptions for unexpected errors, not as regular flow control
- Ignoring SQLCODE and SQLERRM – Always capture error codes and messages for better debugging
Conclusion
Exception handling in Oracle Database 23ai PL/SQL is crucial for writing resilient programs. You have seen how to handle built-in exceptions like NO_DATA_FOUND, TOO_MANY_ROWS, and DUP_VAL_ON_INDEX, how to use WHEN OTHERS for generic handling, create custom exceptions, and manage errors with JSON and vector operations. By logging exceptions and handling them properly, your applications can continue to run smoothly even when errors occur.
