Predefined Exceptions

Predefined exceptions are internally defined exceptions that have predefined names, which PL/SQL declares globally in the package STANDARD. The runtime system raises predefined exceptions implicitly (automatically). Because predefined exceptions have names, you can write exception handlers specifically for them.

Table 12-3 lists the names and error codes of the predefined exceptions.

Table 12-3 PL/SQL Predefined Exceptions

Exception Name	Oracle Error	Error Code
`ACCESS_INTO_NULL`	`ORA-06530`	`-6530`
`CASE_NOT_FOUND`	`ORA-06592`	`-6592`
`COLLECTION_IS_NULL`	`ORA-06531`	`-6531`
`CURSOR_ALREADY_OPEN`	`ORA-06511`	`-6511`
`DUP_VAL_ON_INDEX`	`ORA-00001`	`-1`
`INVALID_CURSOR`	`ORA-01001`	`-1001`
`INVALID_NUMBER`	`ORA-01722`	`-1722`
`LOGIN_DENIED`	`ORA-01017`	`-1017`
`NO_DATA_FOUND`	`ORA-01403`	`+100`
`NO_DATA_NEEDED`	`ORA-06548`	`-6548`
`NOT_LOGGED_ON`	`ORA-01012`	`-1012`
`PROGRAM_ERROR`	`ORA-06501`	`-6501`
`ROWTYPE_MISMATCH`	`ORA-06504`	`-6504`
`SELF_IS_NULL`	`ORA-30625`	`-30625`
`STORAGE_ERROR`	`ORA-06500`	`-6500`
`SUBSCRIPT_BEYOND_COUNT`	`ORA-06533`	`-6533`
`SUBSCRIPT_OUTSIDE_LIMIT`	`ORA-06532`	`-6532`
`SYS_INVALID_ROWID`	`ORA-01410`	`-1410`
`TIMEOUT_ON_RESOURCE`	`ORA-00051`	`-51`
`TOO_MANY_ROWS`	`ORA-01422`	`-1422`
`VALUE_ERROR`	`ORA-06502`	`-6502`
`ZERO_DIVIDE`	`ORA-01476`	`-1476`

Example 12-6 calculates a price-to-earnings ratio for a company. If the company has zero earnings, the division operation raises the predefined exception ZERO_DIVIDE and the executable part of the block transfers control to the exception-handling part.

Example 12-7 uses error-checking code to avoid the exception that Example 12-6 handles.

In Example 12-8, the procedure opens a cursor variable for either the EMPLOYEES table or the DEPARTMENTS table, depending on the value of the parameter discrim. The anonymous block invokes the procedure to open the cursor variable for the EMPLOYEES table, but fetches from the DEPARTMENTS table, which raises the predefined exception ROWTYPE_MISMATCH.

See Also:

"Raising Internally Defined Exception with RAISE Statement"
Database Error Messages to find more information about individual exceptions by searching the Oracle Error number

Example 12-6 Anonymous Block Handles ZERO_DIVIDE

DECLARE
  stock_price   NUMBER := 9.73;
  net_earnings  NUMBER := 0;
  pe_ratio      NUMBER;
BEGIN
  pe_ratio := stock_price / net_earnings;  -- raises ZERO_DIVIDE exception
  DBMS_OUTPUT.PUT_LINE('Price/earnings ratio = ' || pe_ratio);
EXCEPTION
  WHEN ZERO_DIVIDE THEN
    DBMS_OUTPUT.PUT_LINE('Company had zero earnings.');
    pe_ratio := NULL;
END;
/

Result:

Company had zero earnings.

Example 12-7 Anonymous Block Avoids ZERO_DIVIDE

DECLARE
  stock_price   NUMBER := 9.73;
  net_earnings  NUMBER := 0;
  pe_ratio      NUMBER;
BEGIN
  pe_ratio :=
    CASE net_earnings
      WHEN 0 THEN NULL
      ELSE stock_price / net_earnings
    END;
END;
/

Example 12-8 Anonymous Block Handles ROWTYPE_MISMATCH

CREATE OR REPLACE PACKAGE emp_dept_data AUTHID DEFINER AS
  TYPE cv_type IS REF CURSOR;
  
  PROCEDURE open_cv (
    cv       IN OUT cv_type,
    discrim  IN     POSITIVE
  );
  END emp_dept_data;
/
 
CREATE OR REPLACE PACKAGE BODY emp_dept_data AS
  PROCEDURE open_cv (
    cv      IN OUT cv_type,
    discrim IN     POSITIVE) IS
  BEGIN
    IF discrim = 1 THEN
    OPEN cv FOR
      SELECT * FROM EMPLOYEES ORDER BY employee_id;
    ELSIF discrim = 2 THEN
      OPEN cv FOR
        SELECT * FROM DEPARTMENTS ORDER BY department_id;
    END IF;
  END open_cv;
END emp_dept_data;
/

Invoke procedure open_cv from anonymous block:

DECLARE
  emp_rec   EMPLOYEES%ROWTYPE;
  dept_rec  DEPARTMENTS%ROWTYPE;
  cv        Emp_dept_data.CV_TYPE;
BEGIN
  emp_dept_data.open_cv(cv, 1);  -- Open cv for EMPLOYEES fetch.
  FETCH cv INTO dept_rec;        -- Fetch from DEPARTMENTS.
  DBMS_OUTPUT.PUT(dept_rec.DEPARTMENT_ID);
  DBMS_OUTPUT.PUT_LINE('  ' || dept_rec.LOCATION_ID);
EXCEPTION
  WHEN ROWTYPE_MISMATCH THEN
     BEGIN
       DBMS_OUTPUT.PUT_LINE
         ('Row type mismatch, fetching EMPLOYEES data ...');
       FETCH cv INTO emp_rec;
       DBMS_OUTPUT.PUT(emp_rec.DEPARTMENT_ID);
       DBMS_OUTPUT.PUT_LINE('  ' || emp_rec.LAST_NAME);
     END;
END;
/

Result:

Row type mismatch, fetching EMPLOYEES data ...
90  King