PLSQL Exceptions

Oracle Exception is an error or issue that occurs during the execution of a PL/SQL (Procedural Language/Structured Query Language) block or an Oracle SQL query.

Types of Exception:

1. System-defined exceptions
2. User-defined exceptions

System (Pre) -defined exceptions

Exception	Oracle Error	SQLCODE	Description
ACCESS_INTO_NULL	6530	-6530	It is raised when a null object is automatically assigned a value.
CASE_NOT_FOUND	6592	-6592	It is raised when none of the choices in the WHEN clause of a CASE statement is selected, and there is no ELSE clause.
COLLECTION_IS_NULL	6531	-6531	It is raised when a program attempts to apply collection methods other than EXISTS to an uninitialized nested table or varray, or the program attempts to assign values to the elements of an uninitialized nested table or varray.
DUP_VAL_ON_INDEX	1	-1	It is raised when duplicate values are attempted to be stored in a column with unique index.
INVALID_CURSOR	1001	-1001	It is raised when attempts are made to make a cursor operation that is not allowed, such as closing an unopened cursor.
INVALID_NUMBER	1722	-1722	It is raised when the conversion of a character string into a number fails because the string does not represent a valid number.
LOGIN_DENIED	1017	-1017	It is raised when a program attempts to log on to the database with an invalid username or password.
NO_DATA_FOUND	1403	100	It is raised when a SELECT INTO statement returns no rows.
NOT_LOGGED_ON	1012	-1012	It is raised when a database call is issued without being connected to the database.
PROGRAM_ERROR	6501	-6501	It is raised when PL/SQL has an internal problem.
ROWTYPE_MISMATCH	6504	-6504	It is raised when a cursor fetches value in a variable having incompatible data type.
SELF_IS_NULL	30625	-30625	It is raised when a member method is invoked, but the instance of the object type was not initialized.
STORAGE_ERROR	6500	-6500	It is raised when PL/SQL ran out of memory or memory was corrupted.
TOO_MANY_ROWS	1422	-1422	It is raised when a SELECT INTO statement returns more than one row.
VALUE_ERROR	6502	-6502	It is raised when an arithmetic, conversion, truncation, or sizeconstraint error occurs.
ZERO_DIVIDE	1476	1476	It is raised when an attempt is made to divide a number by zero.

Example

DECLARE
v_employee_name employees.last_name%TYPE;
BEGIN
-- Attempt to select an employee's name based on their ID
SELECT last_name INTO v_employee_name FROM employees WHERE employee_id = 101;

-- Print the employee's name
DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_employee_name);

EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No employee found with the given ID.');
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('Query returned more than one row.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An unexpected error occurred: ' || SQLERRM);
END;
/

User-defined exceptions

user-defined exceptions allow developers to define and handle custom errors in their programs.

Example:

DECLARE
-- Declare a user-defined exception
salary_too_high EXCEPTION;

-- Declare a constant for the salary limit
v_salary_limit NUMBER := 10000;

-- Declare a variable for employee salary
v_salary employees.salary%TYPE;

BEGIN
-- Fetch employee salary
SELECT salary INTO v_salary FROM employees WHERE employee_id = 101;

-- Check if salary exceeds the limit
IF v_salary > v_salary_limit THEN
-- Raise the user-defined exception
RAISE salary_too_high;
END IF;

DBMS_OUTPUT.PUT_LINE('Employee salary is within the limit.');

EXCEPTION
-- Handle the user-defined exception
WHEN salary_too_high THEN
DBMS_OUTPUT.PUT_LINE('Error: Salary exceeds the limit of ' || v_salary_limit || '.');
-- Handle other predefined exceptions, if necessary
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An unexpected error occurred: ' || SQLERRM);
END;
/