Exception handling in PL/SQL

Exception happens when a program terminated abnomrally due to an unexpected flow during runtime. To suppress the abnormal termination with an alternative action we use exception handlers.

Exception handling is one of the important steps of database programming. Unhandled exceptions can result in unplanned application outages, impact business continuity, and frustrate end users.

There are two types of exceptions:

• system-defined:

  Implicitly raised by Oracle Database.

• user-defined:

  A user-defined exception is explicitly declared and raised within the program unit.

Let see an example of System-defined exception.

System-defined exceptions:

• These exceptions are defined and maintained implicitly by the Oracle Database and are defined in the Oracle STANDARD package.
• Whenever an exception occurs inside a program, the database picks up the appropriate one from an already defined list of exceptions.
• All system-defined exceptions are associated with a negative error code (except 1 to 100) and a short name. The short name is what we use while specifying the exception handlers.

For example, we have a table named EMP, with 100 employee records, with empployee ids as 1-100. In the following PL/SQL program, we include a SELECT statement to retreive the details of employee id 101. It raises NO_DATA_FOUND exception because a row with employee id 101 doesn't exist.

SET SERVEROUTPUT ON

DECLARE
	LOCAL_ENAME VARCHAR2 (100);
	LOCAL_SAL NUMBER;
	LOCAL_EMPID NUMBER := 101;

BEGIN
	SELECT ENAME, SAL
	INTO LOCAL_ENAME, LOCAL_SAL
	FROM EMP
	WHERE EMPNO = LOCAL_EMPID;
END;
/

You will get the output error message similar to this:

DECLARE
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 8

Let us rewrite the same PL/SQL block to include an EXCEPTION section and handle the NO_DATA_FOUND exception (note that we are using the short name (NO_DATA_FOUND) of exception we are expecting in our code block and want to handle):

SET SERVEROUTPUT ON

DECLARE
	LOCAL_ENAME VARCHAR2 (100);
	LOCAL_SAL NUMBER;
	LOCAL_EMPID NUMBER := 101;
BEGIN
	SELECT ENAME, SAL
	INTO LOCAL_ENAME, LOCAL_SAL
	FROM EMP
	WHERE EMPNO = LOCAL_EMPID;
EXCEPTION
	WHEN NO_DATA_FOUND THEN
	DBMS_OUTPUT.PUT_LINE ('No record found with the employee id: '||LOCAL_EMPID);
END;
/

This time you will not get the default error message, but the flow will cath-up in the exception block and run our logic which will display a customized message like this:

No record found with the employee id: 101
PL/SQL procedure successfully completed.

Related Post(s):

• User-defined exceptions in PL/SQL