Understanding Exceptions

This section provides an overview of exceptions in PL/SQL programming.

About Exceptions

An exception is a PL/SQL error that is raised during program execution, either implicitly by TimesTen or explicitly by your program. Handle an exception by trapping it with a handler or propagating it to the calling environment.

For example, if your SELECT statement returns multiple rows, TimesTen returns an error (exception) at runtime. As the following example shows, you would see TimesTen error 8507, then the associated ORA error message. (ORA messages, originally defined for Oracle Database, are similarly implemented by TimesTen.)

Command> DECLARE
          v_lname VARCHAR2 (15);
         BEGIN
          SELECT last_name INTO v_lname
          FROM employees
          WHERE first_name = 'John';
           DBMS_OUTPUT.PUT_LINE ('Last name is :' || v_lname);
          END;
          /

 8507: ORA-01422: exact fetch returns more than requested number of rows
 8507: ORA-06512: at line 4
The command failed.

You can handle such exceptions in your PL/SQL block so that your program completes successfully. For example:

Command> DECLARE
          v_lname VARCHAR2 (15);
         BEGIN
          SELECT last_name INTO v_lname
          FROM employees
          WHERE first_name = 'John';
           DBMS_OUTPUT.PUT_LINE ('Last name is :' || v_lname);
         EXCEPTION
           WHEN TOO_MANY_ROWS THEN
           DBMS_OUTPUT.PUT_LINE (' Your SELECT statement retrieved multiple
           rows. Consider using a cursor.');
         END;
         /
 Your SELECT statement retrieved multiple rows. Consider using a cursor.
 
PL/SQL procedure successfully completed.

Exception Types

There are three types of exceptions.

Predefined exceptions are error conditions that are defined by PL/SQL.
Non-predefined exceptions include any standard TimesTen errors.
User-defined exceptions are exceptions specific to your application.

In TimesTen, these three types of exceptions are used in the same way as in Oracle Database.

Exception	Description	How to Handle
Predefined TimesTen error	One of approximately 20 errors that occur most often in PL/SQL code	You are not required to declare these exceptions. They are predefined by TimesTen. TimesTen implicitly raises the error.
Non-predefined TimesTen error	Any other standard TimesTen error	These must be declared in the declarative section of your application. TimesTen implicitly raises the error and you can use an exception handler to catch the error.
User-defined error	Error defined and raised by the application	These must be declared in the declarative section. The developer raises the exception explicitly.