Sample Script 2 for Anonymous Blocks

Exceptions let you handle Oracle Database error conditions within PL/SQL program logic. This allows your application to prevent the server from issuing an error that could cause the client application to end. The following anonymous block handles the predefined Oracle Database exception NO_DATA_FOUND (which would result in an ORA-01403 error if not handled):

DECLARE
Emp_number INTEGER := 9999;
Emp_name VARCHAR2(10);
BEGIN
SELECT Ename INTO Emp_name FROM Emp_tab
WHERE Empno = Emp_number; -- no such number
DBMS_OUTPUT.PUT_LINE('Employee name is ' || Emp_name);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No such employee: ' || Emp_number);
END;


You can also define your own exceptions, declare them in the declaration part of a block, and define them in the exception part of the block. An example follows:

DECLARE
Emp_name VARCHAR2(10);
Emp_number INTEGER;
Empno_out_of_range EXCEPTION;
BEGIN
Emp_number := 10001;
IF Emp_number > 9999 OR Emp_number < 1000 THEN
RAISE Empno_out_of_range;
ELSE
SELECT Ename INTO Emp_name FROM Emp_tab
WHERE Empno = Emp_number;
DBMS_OUTPUT.PUT_LINE('Employee name is ' || Emp_name);
END IF;
EXCEPTION
WHEN Empno_out_of_range THEN
DBMS_OUTPUT.PUT_LINE('Employee number ' || Emp_number ||
' is out of range.');
END;