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 lists the names and error codes of the predefined exceptions.
Table: PL/SQL Predefined Exceptions
Exception Name Error Code
ACCESS_INTO_NULL
-6530
CASE_NOT_FOUND
-6592
COLLECTION_IS_NULL
-6531
CURSOR_ALREADY_OPEN
-6511
DUP_VAL_ON_INDEX
-1
INVALID_CURSOR
-1001
INVALID_NUMBER
-1722
LOGIN_DENIED
-1017
NO_DATA_FOUND
+100
NO_DATA_NEEDED
-6548
NOT_LOGGED_ON
-1012
PROGRAM_ERROR
-6501
ROWTYPE_MISMATCH
-6504
SELF_IS_NULL
-30625
STORAGE_ERROR
-6500
SUBSCRIPT_BEYOND_COUNT
-6533
SUBSCRIPT_OUTSIDE_LIMIT
-6532
SYS_INVALID_ROWID
-1410
TIMEOUT_ON_RESOURCE
-51
TOO_MANY_ROWS
-1422
VALUE_ERROR
-6502
ZERO_DIVIDE
-1476
Example : 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 : 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 uses error-checking code to avoid the exception that Example 11-6 handles.
Example : 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;
/
