Database Interaction with PL/SQL: Pre-defined Exceptions

Please note that all the examples in this series have been tested only with Oracle 10g and not with all the previous versions of Oracle. Refer to the documentation of the respective version you are using if any programs fail to execute.

Introduction to Exception Handling

Before going to exception handling, let us first determine all the possible ways to get errors. Errors may occur in PL/SQL (of course, not only PL/SQL) in several possible ways including hardware failures, network failures, bugs in programs, out of memory and several other reasons. We may not know which error has been raised at what moment. But we need to handle all such errors and provide some meaningful messages to the user (instead of making the user horrified with jargon-filled error messages).

An exception is generally a runtime error which gets raised by PL/SQL runtime when a PL/SQL block is in the process of execution. Handling the exception consists of trapping the error (or exception) and providing a meaningful explanation to the user without showing the weird error message raised by the PL/SQL run-time. This doesn't mean only providing meaningful explanations; we can take certain actions (programmatically) when an error occurs at runtime.

Basically, there exist two types of exceptions:

1. Predefined Exceptions
   
   Predefined exceptions are the ones which are already defined by Oracle team that correspond to the most common oracle errors (like division by zero, out of memory, etc.). We can handle them directly within our program without declaring them, and we have thousands of such type of exceptions. In this article, I cover the most important ones.
   
   
2. User-Defined Exceptions
   
   User-Defined Exceptions must be declared and raised explicitly by the user (by issuing RAISE statements). These exceptions are created, used, raised and implemented by user himself. Oracle will not know about any of those exceptions (till it finds the declarations of those exceptions within the PL/SQL block). There exists EXCEPTION section within the PL/SQL block to handle any sort of exceptions and the section is optional. Let us start by looking into some of the most commonly used predefined exceptions.