Trapping Exceptions

This section describes how to trap predefined TimesTen errors or user-defined errors.

Trapping Predefined TimesTen Errors

Trap a predefined TimesTen error by referencing its predefined name in your exception-handling routine. PL/SQL declares predefined exceptions in the STANDARD package.

Refer to the following for details:

Predefined Exceptions Reference

There are predefined exceptions supported by TimesTen.

Table 4-1 provides associated ORA error numbers and SQLCODE values, and descriptions of the exceptions.

Also see Unsupported Predefined Errors.

Table 4-1 Predefined Exceptions

Exception name	Oracle Database Error Number	SQLCODE	Description
`ACCESS_INTO_NULL`	`ORA-06530`	-6530	Program attempted to assign values to the attributes of an uninitialized object.
`CASE_NOT_FOUND`	`ORA-06592`	-6592	None of the choices in the `WHEN` clauses of a `CASE` statement is selected and there is no `ELSE` clause.
`COLLECTION_IS_NULL`	`ORA-06531`	-6531	Program attempted to apply collection methods other than EXISTS to an uninitialized nested table or varray, or program attempted to assign values to the elements of an uninitialized nested table or varray.
`CURSOR_ALREADY_OPENED`	`ORA-06511`	-6511	Program attempted to open an already opened cursor.
`DUP_VAL_ON_INDEX`	`ORA-00001`	-1	Program attempted to insert duplicate values in a column that is constrained by a unique index.
`INVALID_CURSOR`	`ORA-01001`	-1001	There is an invalid cursor operation.
`INVALID_NUMBER`	`ORA-01722`	-1722	Conversion of character string to number failed.
`NO_DATA_FOUND`	`ORA-01403`	+100	Single row `SELECT` returned no rows or your program referenced a deleted element in a nested table or an uninitialized element in an associative array (index-by table).
`PROGRAM_ERROR`	`ORA-06501`	-6501	PL/SQL has an internal problem.
`ROWTYPE_MISMATCH`	`ORA-06504`	-6504	Host cursor variable and PL/SQL cursor variable involved in an assignment statement have incompatible return types.
`STORAGE_ERROR`	`ORA-06500`	-6500	PL/SQL ran out of memory or memory was corrupted.
`SUBSCRIPT_BEYOND_COUNT`	`ORA-06533`	-6533	A program referenced a nested table or varray using an index number larger than the number of elements in the collection.
`SUBSCRIPT_OUTSIDE_LIMIT`	`ORA-06532`	-6532	A program referenced a nested table or varray element using an index number that is outside the valid range (for example, -1).
`SYS_INVALID_ROWID`	`ORA-01410`	-1410	The conversion of a character string into a universal rowid failed because the character string does not represent a `ROWID` value.
`TOO_MANY_ROWS`	`ORA-01422`	-1422	Single row `SELECT` returned multiple rows.
`VALUE_ERROR`	`ORA-06502`	-6502	An arithmetic, conversion, truncation, or size constraint error occurred.
`ZERO_DIVIDE`	`ORA-01476`	-1476	A program attempted to divide a number by zero.

Predefined Exception Example

In this example, a PL/SQL program attempts to divide by 0. The ZERO_DIVIDE predefined exception is used to trap the error in an exception-handling routine.

Command> DECLARE v_invalid PLS_INTEGER;
         BEGIN
           v_invalid := 100/0;
         EXCEPTION
         WHEN ZERO_DIVIDE THEN
           DBMS_OUTPUT.PUT_LINE ('Attempt to divide by 0');
         END;
         /
Attempt to divide by 0
 
PL/SQL procedure successfully completed.

Trapping User-Defined Exceptions

You can define your own exceptions in PL/SQL in TimesTen, and you can raise user-defined exceptions explicitly with either the PL/SQL RAISE statement or the RAISE_APPLICATION_ERROR procedure.

These processes are described in the following sections:

Using the RAISE Statement

The RAISE statement stops execution of a PL/SQL block or subprogram and transfers control to an exception handler. RAISE statements can raise predefined exceptions, or user-defined exceptions whose names you decide.

In the example that follows, the department number 500 does not exist, so no rows are updated in the departments table. The RAISE statement is used to explicitly raise an exception and display an error message, returned by the SQLERRM built-in function, and an error code, returned by the SQLCODE built-in function. Use the RAISE statement by itself within an exception handler to raise the same exception again and propagate it back to the calling environment.

Command> DECLARE
           v_deptno NUMBER := 500;
           v_name VARCHAR2 (20) := 'Testing';
           e_invalid_dept EXCEPTION;
          BEGIN
           UPDATE departments
           SET department_name = v_name
           WHERE department_id = v_deptno;
          IF SQL%NOTFOUND THEN
             RAISE e_invalid_dept;
          END IF;
          ROLLBACK;
          EXCEPTION
            WHEN e_invalid_dept THEN
            DBMS_OUTPUT.PUT_LINE ('No such department');
            DBMS_OUTPUT.PUT_LINE (SQLERRM);
            DBMS_OUTPUT.PUT_LINE (SQLCODE);
         END;
         /
No such department
User-Defined Exception
1
 
PL/SQL procedure successfully completed.
 
The command succeeded.

Note:

Given the same error condition in TimesTen and Oracle Database, SQLCODE returns the same error code, but SQLERRM does not necessarily return the same error message. This is also noted in TimesTen Error Messages and SQL Codes.

Using the RAISE_APPLICATION_ERROR Procedure

Use the RAISE_APPLICATION_ERROR procedure in the executable section or exception section (or both) of your PL/SQL program. TimesTen reports errors to your application so you can avoid returning unhandled exceptions.

Use an error number between -20,000 and -20,999. Specify a character string up to 2,048 bytes for your message.

The following example attempts to delete from the employees table where last_name=Patterson. The RAISE_APPLICATION_ERROR procedure raises the error, using error number -20201.

Command> DECLARE
           v_last_name employees.last_name%TYPE := 'Patterson';
         BEGIN
         DELETE FROM employees WHERE last_name = v_last_name;
         IF SQL%NOTFOUND THEN
           RAISE_APPLICATION_ERROR (-20201, v_last_name || ' does not exist');
         END IF;
         END;
         /
 8507: ORA-20201: Patterson does not exist
 8507: ORA-06512: at line 6
The command failed.