In PL/SQL, when an error occurs, the PL/SQL block (stored procedure, trigger, etc.) will generate an error and terminate. However, it is often desirable for the PL/SQL block to perform some action when it reaches an error. For instance, you may open a cursor and fetch results only to find no rows of data returned from the cursor. This is the NO_DATA_FOUND exception. If no data is found, then your PL/SQL block may want to issue an message to the application. The exception handler is the routine you code in your PL/SQL block to handle the error in the manner you choose.
Out of the box, Oracle comes with many predefined exceptions. However, your application may need other exception handling. You are free to code your own exceptions, or user-defined exceptions. As an example, consider a database for a university. You may have a stored procedure where a student drops a class. The student is not allowed to drop the class after the first three weeks of the semester. A user-defined exception in the stored procedure would verify that this condition is being met. If the condition is not met, the user-defined exception is raised.
This document gives more details on user-defined exceptions, along with a few examples.
This was first published in January 2007