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 |
---|---|---|---|
|
|
-6530 |
Program attempted to assign values to the attributes of an uninitialized object. |
|
|
-6592 |
None of the choices in the |
|
|
-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. |
|
|
-6511 |
Program attempted to open an already opened cursor. |
|
|
-1 |
Program attempted to insert duplicate values in a column that is constrained by a unique index. |
|
|
-1001 |
There is an invalid cursor operation. |
|
|
-1722 |
Conversion of character string to number failed. |
|
|
+100 |
Single row |
|
|
-6501 |
PL/SQL has an internal problem. |
|
|
-6504 |
Host cursor variable and PL/SQL cursor variable involved in an assignment statement have incompatible return types. |
|
|
-6500 |
PL/SQL ran out of memory or memory was corrupted. |
|
|
-6533 |
A program referenced a nested table or varray using an index number larger than the number of elements in the collection. |
|
|
-6532 |
A program referenced a nested table or varray element using an index number that is outside the valid range (for example, -1). |
|
|
-1410 |
The conversion of a character string into a universal rowid failed because the character string does not represent a |
|
|
-1422 |
Single row |
|
|
-6502 |
An arithmetic, conversion, truncation, or size constraint error occurred. |
|
|
-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.