March 20, 2021

User-defined exceptions in PL/SQL

If you are required to standardize the exception handling, not just to control the abnormal program flow but also to alter the program execution logic, you need to use user-defined exceptions. The user-defined exceptions are raised in the BEGIN..END section of the block using the RAISE statement.

With custom exceptions, Oracle allows users to specify names, associate error codes, and raise statements in line with the implementation logic.

There are three ways to declare user-defined exceptions.

  • EXCEPTION type variable:

    • Declare the EXCEPTION type variable in the declaration section.
    • Raise it explicitly in the program body using the RAISE statement.
    • Handle it in the EXCEPTION section. No error code will be involved here.
  • EXCEPTION variable:

    Declare the EXCEPTION variable and associate it with a standard error number using PRAGMA EXCEPTION_INIT.
  • RAISE_APPLICATION_ERROR:

    Use the RAISE_APPLICATION_ERROR to declare a dedicated error number and error message.

Related Post(s):

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):