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.
Dig Deeper on Using Oracle PL-SQL
Related Q&A from Brian Peasland
Oracle expert Brian Peasland answers one reader's question about common pitfalls when connecting Oracle to outside programs. Continue Reading
One reader asks expert Brian Peasland a question about datafile sizes with the Oracle RMAN duplicate 10g command. Continue Reading
Managing parent table-child table relations in Oracle SQL environments is key to efficient programming. Continue Reading