Exception (error) Handling

Exceptions are identifiers in PL/SQL that are raised during the execution of a block to terminate its action. A block is always terminated when PL/SQL raises an exception but you can define your own error handler to capture exceptions and perform some final actions before quitting the block. If PL/SQL handles the exception within the block then the exception will not propagate out to an enclosing block or environment.

There are two classes of exceptions, these are :-

Predefined – Oracle predefined errors which are associated with specific error codes.

User-defined – Declared by the user and raised when specifically requested within a block. You may associate a user-defined exception with an error code if you wish.

If an error occurs within a block PL/SQL passes control to the EXCEPTION section of the block. If no EXCEPTION section exists within the block or the EXCEPTION section doesn’t handle the error that’s occurred then the block is terminated with an unhandled exception. Exceptions propagate up through nested blocks until an exception handler is found that can handle the error. If no exception handler is found in any block the error is passed out to the host environment. Exceptions occur when either an Oracle error occurs (this automatically raises an exception) or you explicitly raise an error using the RAISE statement.

The two most common errors originating from a SELECT statement occur when it returns no rows or more than one row (remember that this is not allowed).

Advertisements

About Oracle Technology Blogs
Oracle Application Development

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: