Lesson 8

In this module, you learned all about error handling. Error handling provides for robust code. PL/SQL provides you with extensive exception handling capabilities. In this module, you learned how to:

1. Define an exception and its purpose
2. Categorize the different types of exceptions
3. Code predefined server exception
4. Use an implicit user-defined exception
5. Raise an exception based on user-defined criteria
6. Describe the use of functions and procedures within error handling

The way SQL*Plus communicates success depends on the class of command you are running. With most SQL*Plus-specific commands, you can calibrate success by the absence of an error message. Successful SQL and PL/SQL commands usually result in some kind of positive textual message. If SQL*Plus encounters an error in a SQL or PL/SQL statement, it will, by default, report the error and continue processing. This behavior is desirable when you are working interactively. But when you’re executing a script, there are many cases in which you want an error to cause SQL*Plus to terminate. Use the following command to make that happen:

SQL> WHENEVER SQLERROR EXIT SQL.SQLCODE

Thereafter in the current session, SQL*Plus terminates if the database server returns any error messages in response to a SQL or PL/SQL statement. The SQL.SQLCODE part means that, when SQL*Plus terminates, it sets its return code to a nonzero value, which you can detect in the calling environment.† Otherwise, SQL*Plus always ends with a 0 return code, which may falsely imply that the script succeeded. Another form of this command is:

SQL> WHENEVER SQLERROR SQL.SQLCODE EXIT ROLLBACK

In this module, you were introduced to the following glossary terms:

1. Exception
2. PRAGMA: A pragma is a special instruction to the compiler. Also called a pseudoinstruction, the pragma does not change the meaning of a program. It simply passes information to the compiler.
3. Warning:A warning issued by the application to the user.

The next module concludes the course.