PL/SQL by Example / Exceptions

As already indicated in the introduction, the part of the block introduced by the keyword EXCEPTION is used for catching program runtime errors. We distinguish between system errors, which are raised automatically when an error occurs, and user-defined errors, which must be raised explicitly using the RAISE command. The following examples describe several of the most common system errors.

System

VALUE_ERROR

DECLARE
  l_Number NUMBER;
BEGIN
  l_Number:='Francis';
EXCEPTION
  WHEN VALUE_ERROR THEN
    dbms_output.put_line('Implicit conversion failed!');
END;

ZERO_DIVIDE

DECLARE
  l_Number NUMBER;
BEGIN
  l_Number:=20/0;
EXCEPTION
  WHEN ZERO_DIVIDE THEN
    dbms_output.put_line('Division by zero!');
END;

NO_DATA_FOUND

DECLARE
  l_Number NUMBER;
BEGIN
  SELECT employee_id
    INTO l_Number
    FROM employees
    WHERE (last_name='Sauce')
  ;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    dbms_output.put_line('Employee Sauce does not exist!');
END;

TOO_MANY_ROWS

DECLARE
  l_Number NUMBER;
BEGIN
  SELECT employee_id
    INTO l_Number
    FROM employees
    WHERE (salary>3000)
  ;
EXCEPTION
  WHEN TOO_MANY_ROWS THEN
    dbms_output.put_line('More than one row!');
END;

COLLECTION_IS_NULL

CREATE OR REPLACE TYPE Varchar_TT IS TABLE OF VARCHAR2(4000);
/

DECLARE
  lt_Array Varchar_TT;
BEGIN
  --lt_Array:=Varchar_TT();
  --lt_Array.EXTEND(1);
  lt_Array(1):='test';
EXCEPTION
  WHEN COLLECTION_IS_NULL THEN
    dbms_output.put_line('Uninitialized object!');
END;

SUBSCRIPT_BEYOND_COUNT

DECLARE
  lt_Array Varchar_TT;
  l_Cnt NUMBER;
BEGIN
  lt_Array:=Varchar_TT();
  lt_Array.EXTEND(1);
  lt_Array(2):='test';
EXCEPTION
  WHEN SUBSCRIPT_BEYOND_COUNT THEN
    dbms_output.put_line('Array index out of bounds!');
END;

OTHERS

It is possible to catch multiple types of errors in one block, however, if an error does not have its own handler, the program will terminate with the general exception OTHERS. The error message is returned by the SQLERRM function.

DECLARE
  lt_Array Varchar_TT;
BEGIN
  lt_Array:=Varchar_TT();
  lt_Array.EXTEND(1);
  lt_Array(2):='test';
EXCEPTION
  WHEN COLLECTION_IS_NULL THEN
    dbms_output.put_line('Uninitialized object!');
  WHEN OTHERS THEN
    dbms_output.put_line(SQLERRM); -- ORA-06533: Subscript beyond count
END;

In situations where we know in advance that an operation will be potentially dangerous, it is good practice to prevent errors rather than just catch them. In very large systems, this simplifies subsequent problem finding.

DECLARE
  l_DangerousNumber NUMBER:=0;
  l_Count PLS_INTEGER;
  l_Result NUMBER;
BEGIN
  -- handling NO_DATA_FOUND and TOO_MANY_ROWS
  SELECT COUNT(1)
    INTO l_Count
    FROM employees
    WHERE (last_name='Russell')
  ;
  IF    (l_Count>0)
    AND (l_Count<2)
  THEN
    -- exactly one exists
    SELECT CASE -- handling ZERO_DIVIDE and NULL values
             WHEN (NVL(l_DangerousNumber, 0)=0)
               THEN 0
             ELSE (salary/l_DangerousNumber)
           END
      INTO l_Result
      FROM employees
      WHERE (last_name='Russell')
    ;
    dbms_output.put_line(l_Result);
  ELSE
    -- does not exist or there are more
    dbms_output.put_line('Number of employees: '||l_Count);
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line('Unknown error!');
END;

User-defined

If the predefined Oracle exceptions are not sufficient for us, the next step is to define our own in the declarative part of an anonymous block or package. The RAISE command is used to raise a user-defined exception.

DECLARE
  MY_EXCEPTION EXCEPTION;
BEGIN
  -- potentially
  -- dangerous
  -- operations
  RAISE MY_EXCEPTION;
EXCEPTION
  WHEN MY_EXCEPTION THEN
    dbms_output.put_line('My exception');
END;

Exception Propagation

The next example explains the system of catching and propagating exceptions through several levels of code blocks. In this case, the exceptions are declared in a separate package.

CREATE OR REPLACE PACKAGE ExceptionsPackage
AS
  OUTER_EXCEPTION EXCEPTION;
  INNER_EXCEPTION EXCEPTION;
END;
/

BEGIN
  /* START OF INNER BLOCK */
  BEGIN
    -- potentially
    -- dangerous
    -- operations
    RAISE ExceptionsPackage.INNER_EXCEPTION;
  EXCEPTION
    WHEN ExceptionsPackage.INNER_EXCEPTION THEN
      dbms_output.put_line('Inner exception');
  END;
  /* END OF INNER BLOCK */
  -- inner exception was caught, continuing
  -- another
  -- dangerous
  -- operation
  RAISE ExceptionsPackage.OUTER_EXCEPTION;
EXCEPTION
  -- OUTER_EXCEPTION has no handler, program ends in the general one
  WHEN OTHERS THEN
    dbms_output.put_line('Another unexpected error');
END;