PL/SQL Exceptions  «Prev  Next»

Lesson 3Types of exceptions
ObjectiveCategorize the different types of exceptions.

PL/SQL Exceptions Types

Now that you know what an exception is, let’s go ahead and categorize them. The following table lists the three types of exceptions.
Exception typeImplicitExplicit
Predefined server exceptions  
Non-predefined server exceptions  
User defined  

Let us review each one of them.

Predefined server exceptions

Eighteen errors are predefined by the Oracle server. As you may have guessed, they are the ones that occur most often in PL/SQL code. You need not declare them, you can let the Oracle server raise them implicitly for you.
Warning: You will still need to write an exception handler in case you wish to trap them, however. View the Table below to see some of the predefined server exceptions available in PL/SQL.
Exception nameOracle server error numberDescription
ACCESS_INTO_NULLORA_06530Tried to assign values to the attributes of an uninitialized object
COLLECTION_IS_NULLORA_06531Tried to apply collection methods other than EXISTS to an uninitialized nested table or varray
CURSOR_ALREADY_OPENORA_06511Tried to open an already open cursor
DUP_VAL_ON_INDEXORA_00001Tried to insert/update a duplicate value in a table with unique index
INVALID_CURSORORA_01001Tried to perform an illegal cursor operation
INVALID_NUMBERORA_01722Tried to convert a nonnumeric character to a number
LOGIN_DENIEDORA_01017Invalid user name or password specified to log in to Oracle
NO_DATA_FOUNDORA_01403SELECT statement returned no data
NOT_LOGGED_ONORA_01012PL/SQL program issued a database call without being connected to Oracle
PROGRAM_ERRORORA_06501PL/SQL had an internal problem
ROWTYPE_MISMATCHORA_06504Host cursor variable and PL/SQL cursor variable involved in an assignment had incompatible return types
STORAGE_ERRORORA_06500PL/SQL ran out of memory or memory was corrupted
SUBSCRIPT_BEYOND_COUNTORA_06533Referenced a nested table or varray element using an index number larger than the number of elements in the collection
SUBCRIPT_OUTSIDE_LIMITORA_06532Referenced a nested table or varray element using an index number outside the legal range
TIMEOUT_ON_RESOURCEORA_00051Time-out occurred while Oracle was waiting for a resource
TOO_MANY_ROWSORA_01422Single-row SELECT returned more than one row
VALUE_ERRORORA_06502Arithmetic, conversion, truncation, or size constraint error occurred
ZERO_DIVIDEORA_01476Attempted to divide by zero

Non-predefined Oracle server exceptions

Any other standard Oracle server error that is not predefined, falls under this category. You need to declare this within the declarative section and allow the Oracle server to raise these exceptions implicitly. Further, you need to write the exception handler to handle the exception.

User-defined exceptions

This is a condition that the developer determines to be abnormal. You need to declare this within the declarative section and raise it explicitly using the RAISE command. Note: In addition to these exceptions, some application tools with client-side PL/SQL, such as Oracle Developer Forms, have their own exceptions. We will take a detailed look at how to code each of these exceptions in the next few lessons. In the next lesson, you will learn to handle predefined server exception.

Identifying Predefined Server Errors

Click the link below to match the different types of predefined server errors with their respective definitions.
Identifying Predefined Server Errors

Identifying predefined Server Errors

The correct descriptions for the predefined server errors are:
  1. NO_DATA_FOUND : SELECT statement returned no data
  2. CURSOR_ALREADY_OPEN : Tried to open an already open cursor
  3. INVALID_CURSOR : Tried to perform an illegal cursor operation
  4. TOO_MANY_ROWS : Single-row SELECT statement returned more than one row
  5. ZERO_DIVIDE : Attempted to divide by zero
  6. DUP_VAL_ON_INDEX : Tried to insert/update a duplicate value in a table with a unique index


Ad Oracle PL/SQL Programming