As the name implies, system-defined exceptions are defined and maintained implicitly by the Oracle Database. They are defined in the Oracle STANDARD package. Whenever an exception occurs inside a program, the database picks up the appropriate exception from the available list. All system-defined exceptions are associated with a negative error code (except 1 to 100) and a short name, which is used while specifying the exception handlers.

For example, the following PL/SQL program includes a SELECT statement to select details of employee 8376. It raises NO_DATA_FOUND exception because employee id 8376 doesn't exist.

SET SERVEROUTPUT ON

/*Declare the PL/SQL block */
DECLARE
   L_ENAME VARCHAR2 (100);
   L_SAL NUMBER;
   L_EMPID NUMBER := 8376;
BEGIN

/*Write a SELECT statement */
   SELECT ENAME, SAL
   INTO L_ENAME, L_SAL
   FROM EMP
   WHERE EMPNO = L_EMPID;
END;
/

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

Let us rewrite the preceding PL/SQL block to include an EXCEPTION section and handle the NO_DATA_FOUND exception:

/*Enable the SERVEROUTPUT parameter to print the results in the environment*/
SET SERVEROUTPUT ON

/*Start the PL/SQL block*/
DECLARE
   /*Declare the local variables*/
   L_ENAME VARCHAR2 (100);
   L_SAL NUMBER;
   L_EMPID NUMBER := 8376;
BEGIN
   /*SELECT statement to fetch the name and salary details of the employee*/
   SELECT ENAME, SAL
   INTO L_ENAME, L_SAL
   FROM EMP
   WHERE EMPNO = L_EMPID;
EXCEPTION
   /*Exception Handler */
   WHEN NO_DATA_FOUND THEN
   /*Display an informative message*/
   DBMS_OUTPUT.PUT_LINE ('No Employee exists with the id '||L_EMPID);
END;
/

No Employee exists with the id 8376

PL/SQL procedure successfully completed.

The following table lists some of the commonly used system-defined exceptions along with their short name and ORA error code:

Oracle allows users to create custom exceptions, specify names, associate error codes, and raise statements in line with the implementation logic. If PL/SQL applications 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.

There are three ways of declaring user-defined exceptions:

The following PL/SQL block declares a user-defined exception and raises it in the program body:

/*Enable the SERVEROUTPUT parameter to print the results in the environment*/
SET SERVEROUTPUT ON

/*Declare a bind variable M_DIVISOR*/
VARIABLE M_DIVISOR NUMBER;

/*Declare a bind variable M_DIVIDEND*/
VARIABLE M_DIVIDEND NUMBER;

/*Assign value to M_DIVISOR as zero*/
EXEC :M_DIVISOR := 0;

PL/SQL procedure successfully completed.

/*Assign value to M_DIVIDEND as 10/
EXEC :M_DIVIDEND := 10;

PL/SQL procedure successfully completed.

/*Start the PL/SQL block*/
DECLARE
   /*Declare the local variables and initialize with the bind variables*/
   L_DIVISOR NUMBER := :M_DIVISOR;
   L_DIVIDEND NUMBER := :M_DIVIDEND;
   L_QUOT NUMBER;
   /*Declare an exception variable*/
   NOCASE EXCEPTION;
BEGIN
   /*Raise the exception if Divisor is equal to zero*/
   IF L_DIVISOR = 0 THEN
      RAISE NOCASE;
   END IF;
   L_QUOT := L_DIVIDEND/L_DIVISOR;
   DBMS_OUTPUT.PUT_LINE('The result : '||L_QUOT);
EXCEPTION
   /*Exception handler for NOCASE exception*/
   WHEN NOCASE THEN
      DBMS_OUTPUT.PUT_LINE('Divisor cannot be equal to zero');
END;
/
Divisor cannot be equal to zero

PL/SQL procedure successfully completed.

/*Assign a non zero value to M_DIVISOR*/
EXEC :M_DIVISOR := 2;

PL/SQL procedure successfully completed.

/*Re-execute the block */
SQL> /
The result : 5

PL/SQL procedure successfully completed.

RAISE_APPLICATION_ERROR (error_number, error_message[, {TRUE | FALSE}])

/*Enable the SERVEROUTPUT parameter to print the results in the environment*/
SET SERVEROUTPUT ON

/*Start the PL/SQL block */
DECLARE

/*Declare the birth date */
   L_DOB_MON DATE := '01-DEC-1981';

/*Declare a cursor to filter employees who were hired on birthday month*/
   CURSOR C IS
    SELECT empno, ename, hiredate
    FROM emp;
BEGIN
   FOR I IN C
   LOOP

   /*Raise exception, if birthdate is later than the hiredate */
      IF i.hiredate < l_dob_mon THEN
        RAISE_APPLICATION_ERROR (-20005,'Hiredate earlier than the given date!! Check for another employee');
      ELSE 
	DBMS_OUTPUT.PUT_LINE(i.ename||'was hired on'||i.hiredate);
      END IF;
   END LOOP;
END;
/

*
ERROR at line 1:
ORA-20005: Hiredate earlier than the given date!! Check for another employee
ORA-06512: at line 11

/*Enable the SERVEROUTPUT parameter to print the results in the environment*/
SQL> SET SERVEROUTPUT ON

/*Start the PL/SQL block */
DECLARE

/*Declare the birth date */
  L_DOB_MON DATE := '01-DEC-1981';

/*Declare the exception variable */
  INVALID_EMP_DATES EXCEPTION;
  PRAGMA EXCEPTION_INIT(INVALID_EMP_DATES,-20005);

/*Declare a cursor to filter employees who were hired on birthday month*/
   CURSOR C IS
    SELECT ename, deptno, hiredate
    FROM emp;
BEGIN
   FOR I IN C
   LOOP
      /*Raise exception, if birthdate is later than the hiredate */
      IF i.hiredate < l_dob_mon THEN
	   RAISE INVALID_EMP_DATES;
      ELSE 
	DBMS_OUTPUT.PUT_LINE(i.ename||'was hired on'||i.hiredate);
      END IF;
   END LOOP;
EXCEPTION
  WHEN INVALID_EMP_DATES THEN
    DBMS_OUTPUT.PUT_LINE(SQLERRM||'Hiredate earlier than the given date!! Check for another employee');
END;
/

ORA-20005: Hiredate earlier than the given date!! Check for another employee

PL/SQL procedure successfully completed.

Until now, we have seen that, as soon as the exception is raised in the procedural section of a PL/SQL block, the control jumps to the exception section and chooses the appropriate exception handler. The non-existence of the exception handler may lead to the abnormal termination of the program.

In the case of nested PL/SQL blocks, if the exception is raised in an inner block, the program control flows down to the exception section of the inner block. If the inner block handles the exception, it is executed and the program control returns to the next executable statement in the outer block.

If the inner block does not handle the exception, the program control continues to search for the appropriate handler and propagates to the exception section of the outer block. Yes, the execution of the outer block is skipped and the program control lands straight in to the exception section. The program control will continue to propagate the unhandled exception in the outer blocks until the appropriate one is found and handled.

For example, the following PL/SQL program contains a child block within the parent block:

/*Parent block*/
DECLARE
...
BEGIN
   /*Outer block executable statements*/
...
   /*Child Block*/
   DECLARE
  ...
   BEGIN
      ...
      /*Inner block executable statements*/
      ...
   EXCEPTION
      /*Inner block exception handlers*/
   END;
   ...
   /*Outer block executable statements*/
EXCEPTION
/*Outer block exception handlers*/
END;

If the exception is raised in one of the /*Inner block executable statements*/, the control flows to /*Inner block exception handlers*/. If the appropriate exception handler is not found, it propagates straight to the /*Outer block exception handlers*/ and execution of /*Outer block executable statements*/ is skipped.

When working with nested PL/SQL blocks, developers must be cautious while coding exception handling logic. The exception propagation should be thoroughly tested to build fail‑proof applications.