PL/SQL Exception Handling

By | July 14, 2015
An exception is a condition which executes when an error occurs during execution of a PL/SQL program. PL/SQL provides a feature that lets the programmers to handle these exceptions in the exception block in a PL/SQL program. Exception block contains the methods (defined by the programmer) which can provide a particular action that needs to be taken to come out of this error or exception and prevent the program from terminating abnormally. This mechanism is known as error handling or exception handling in PL/SQL.

Generally, there are two types of exceptions:

1. System-defined Exceptions

2. User-defined Exceptions

System-defined Exceptions

These are pre-defined exceptions situated in Oracle SQL. It is involuntarily raised or executed on encountering a program that violates any Oracle SQL rules.

System-defined exceptions normally are of two types:

1. Named Exceptions
These are system defined exceptions which are pre-defined in the Oracle SQL and normally have a name for reference intention. Few of the named exceptions are as follows:

a. ZERO_DIVIDE: This error occurs when a user tries to divide a number by zero.

b. NO_DATA_FOUND: This error occurs when a user fires a query that doesn’t return anything.

c. CURSOR_ALREADY_OPEN: This error occurs when you try to access a Cursor which is already open or which is already being used by any other program.

d. TOO_MANY_ROWS: This error is raised when you try to fetch more than a single row into a variable or a record.

e. LOGIN_DENIED: This error is raised when a user tries to log into the Oracle database with a wrong username or a password.

f. INVALID_CURSOR: This error occurs when you perform an invalid task on a cursor like fetching data from a cursor that is closed.

g. STORAGE_ERROR: This error occurs when PL/SQL database runs out of memory or memory gets malfunctioned.

2. Unnamed Exceptions
These are the system defined exceptions that the Oracle provides to its users. These exceptions don’t have a naming system to its block structure. However, such exceptions do have an error code and an error message associated to it. 

Such exceptions are handled either by associating the exception code to a name and using it as a named exception or by using the WHEN other THEN exception handler mechanism.

However, Oracle provides us a feature to assign a name to an unnamed System exception which includes the usage of a Pragma which is also known as an Exception_Init. This mechanism is used to link the Oracle system error code to user defined exception name.

Syntax using Exception_Init

User-defined Exceptions

The user-defined exceptions are the ones that are developed or programmed by the programmer. These are explicitly declared in the declaration section and explicitly raised in the execution section.

A PL/SQL exception definition contains parts such as exception type, error code/ number and an error message for the end-user. Every exception whether it is user-defined or pre-defined (System) has an error code associated with it.

Syntax for User-defined Exceptions



PLSQL Exception Handling

RAISE_APPLICATION_ERROR() is an in-built procedure which is used to display the user-defined error messages along with an error code which falls in the range of -20000 to -20999. It is only used to raise an exception and there is no method to handle it. Exceptions are raised by the Oracle database server automatically whenever there is any database malfunctioning. However, exceptions can be raised by the programmers by using the RAISE command.


Leave a Reply

Your email address will not be published. Required fields are marked *