This chapter explores the flexible error trapping and error handling you can use in your PL/SQL programs.
For more information on error-handling and exceptions in PL/SQL, see "PL/SQL Error Handling" in Oracle Database PL/SQL Language Reference.
See the end of this chapter for TimesTen-specific considerations.
The following topics are covered:
This section provides an overview of exceptions in PL/SQL programming, covering the following topics:
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.
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. | 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. | Must be declared in the declarative section. Developer raises the exception explicitly. |
This section describes how to trap predefined TimesTen errors or user-defined 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.
Table 4-1 lists predefined exceptions supported by TimesTen, the 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 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 |
A program attempted to open an already opened cursor. |
|
|
|
-1 |
A program attempted to insert duplicate values in a column that is constrained by a unique index. |
|
|
|
-1001 |
Illegal 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 legal range (for example, -1). |
|
|
|
-1410 |
The conversion of a character string into a universal rowid failed because the character string does not represent a value |
|
|
|
-1422 |
Single row |
|
|
|
-6502 |
An arithmetic, conversion, truncation, or size constraint error occurred. |
|
|
|
-1476 |
A program attempted to divide a number by zero. |
Example 4-1 Using the ZERO_DIVIDE predefined exception
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.
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.
The RAISE statement stops normal 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.
Example 4-2 Using RAISE statement to trap user-defined exception
In this example, 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 will return the same error code, but SQLERRM will not necessarily return the same error message. This is also noted in "TimesTen error messages and SQL codes".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.
Example 4-3 Using the RAISE_APPLICATION_ERROR procedure
This 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.
You can use the show errors command in ttIsql to see details about errors you encounter in executing anonymous blocks or compiling packages, procedures, or functions. This is shown in Example 4-4.
Example 4-4 ttIsql show errors command
Again consider Example 2-17. Assume the same package specification shown there, which declares the procedures and functions hire_employee, remove_employee, and num_above_salary. But instead of the body definition shown there, consider the following, which defines hire_employee and num_above_salary but not remove_employee:
CREATE OR REPLACE PACKAGE BODY emp_actions AS
-- Code for procedure hire_employee:
PROCEDURE hire_employee (employee_id NUMBER,
last_name VARCHAR2,
first_name VARCHAR2,
email VARCHAR2,
phone_number VARCHAR2,
hire_date DATE,
job_id VARCHAR2,
salary NUMBER,
commission_pct NUMBER,
manager_id NUMBER,
department_id NUMBER) IS
BEGIN
INSERT INTO employees VALUES (employee_id,
last_name,
first_name,
email,
phone_number,
hire_date,
job_id,
salary,
commission_pct,
manager_id,
department_id);
END hire_employee;
-- Code for function num_above_salary:
FUNCTION num_above_salary (emp_id NUMBER) RETURN NUMBER IS
emp_sal NUMBER(8,2);
num_count NUMBER;
BEGIN
SELECT salary INTO emp_sal FROM employees
WHERE employee_id = emp_id;
SELECT COUNT(*) INTO num_count FROM employees
WHERE salary > emp_sal;
RETURN num_count;
END num_above_salary;
END emp_actions;
/
Attempting this body definition after the original package specification results in the following:
Warning: Package body created with compilation errors.
To get more information, run ttIsql and use the command show errors. In this example, show errors provides the following:
Command> show errors; Errors for PACKAGE BODY EMP_ACTIONS: LINE/COL ERROR -------- ----------------------------------------------------------------- 13/13 PLS-00323: subprogram or cursor 'REMOVE_EMPLOYEE' is declared in a package specification and must be defined in the package body
You should be aware of some error-related behaviors that differ between TimesTen PL/SQL and Oracle PL/SQL:
TimesTen PL/SQL transaction and rollback behavior for unhandled exceptions
Possibility of runtime errors after clean compile (use of Oracle SQL parser)
TimesTen PL/SQL differs from Oracle PL/SQL in a scenario where an application executes PL/SQL in the middle of a transaction, and an unhandled exception occurs during execution of the PL/SQL. Oracle will roll back to the beginning of the anonymous block. TimesTen will not roll back.
An application should always handle any exception that results from execution of a PL/SQL block, as in the following example, run with autocommit disabled:
create table mytable (num int not null primary key);
set serveroutput on
insert into mytable values(1);
begin
insert into mytable values(2);
insert into mytable values(1);
exception
when dup_val_on_index then
dbms_output.put_line('oops:' || sqlerrm);
rollback;
end;
/
select * from mytable;
commit;
The second INSERT will fail because values must be unique, so there will be an exception and the program will perform a rollback. Running this in TimesTen results in the following.
oops:TT0907: Unique constraint (MYTABLE) violated at Rowid <BMUFVUAAABQAAAADjq> PL/SQL procedure successfully completed. select * from mytable; 0 rows found.
The result is equivalent in Oracle Database, with the SELECT results showing no rows.
Now consider a TimesTen example where the exception is not handled, again run with autocommit disabled:
create table mytable (num int not null primary key); set serveroutput on insert into mytable values(1); begin insert into mytable values(2); insert into mytable values(1); end; / select * from mytable; commit;
In TimesTen, the SELECT query will indicate execution of the first two inserts:
907: Unique constraint (MYTABLE) violated at Rowid <BMUFVUAAABQAAAADjq> 8507: ORA-06512: at line 3 The command failed. select * from mytable; < 1 > < 2 > 2 rows found.
If you execute this in Oracle, there will be a rollback to the beginning of the PL/SQL block, so the SELECT results will indicate execution of only the first insert:
ORA-00001: unique constraint (SYSTEM.SYS_C004423) violated
ORA-06512: at line 3
NUM
----------
1
Notes:
If there is an unhandled exception in a PL/SQL block, TimesTen leaves the transaction open only to allow the application to assess its state and determine appropriate action.
An application in TimesTen should not execute a PL/SQL block while there are uncommitted changes in the current transaction, unless those changes together with the PL/SQL operations really do constitute a single logical unit of work and the application will be able to determine appropriate action. Such action, for example, might consist of a rollback to the beginning of the transaction.
If autocommit is enabled and an unhandled exception occurs in TimesTen, the entire transaction will be rolled back.
Given the same error condition, TimesTen does not guarantee that the error message returned by TimesTen will be the same as the message returned by Oracle Database, although the SQL code will be the same. Therefore, the information returned by the SQLERRM function may be different, but that returned by the SQLCODE function will be the same.
For further information:
Example 4-2 uses SQLERRM and SQLCODE.
Refer to "Warnings and Errors" in Oracle TimesTen In-Memory Database Error Messages and SNMP Traps for information about specific TimesTen error messages.
Refer to "SQLERRM Function" and "SQLCODE Function" in Oracle Database PL/SQL Language Reference for general (and Oracle-specific) information.
Oracle Database does not have the concept of runtime warnings, so Oracle PL/SQL does not support warnings.
TimesTen In-Memory Database does have the concept of warnings, but because the TimesTen PL/SQL implementation is based on the Oracle PL/SQL implementation, TimesTen PL/SQL does not support warnings.
As a result, in TimesTen you could execute a SQL statement and see a resulting warning, but if you execute the same statement through PL/SQL you will not see the warning.
"Trapping predefined TimesTen errors" lists predefined exceptions supported by TimesTen, the associated ORA error numbers and SQLCODE values, and descriptions of the exceptions.
Table 4-2 notes predefined exceptions that are not supported by TimesTen.
Table 4-2 Predefined exceptions not supported by TimesTen
| Exception name | Oracle error number | SQLCODE | Description |
|---|---|---|---|
|
|
|
-1017 |
Invalid user name and password. |
|
|
|
-1012 |
A program issued a database call without being connected to the database. |
|
|
|
-30625 |
A program attempted to invoke a |
|
|
|
-51 |
A timeout occurred while the database was waiting for a resource. |
The TimesTen PL/SQL implementation uses the Oracle SQL parser in compiling PL/SQL programs. (This is discussed in "PL/SQL in TimesTen versus PL/SQL in Oracle Database".) As a result, if your program uses Oracle syntax or Oracle built-in procedures that are not supported by TimesTen, the issue will not be discovered during compilation. A runtime error would occur during program execution, however.
TimesTen SQL includes several constructs that are not present in Oracle SQL. The PL/SQL language does not include these constructs. To use TimesTen-specific SQL from PL/SQL, execute the SQL statements using the EXECUTE IMMEDIATE statement. This will avoid compilation errors.
For lists of TimesTen-specific SQL and expressions, see "Compatibility Between TimesTen and Oracle" in Oracle In-Memory Database Cache User's Guide.
For more information about EXECUTE IMMEDIATE, refer to "Dynamic SQL in PL/SQL (EXECUTE IMMEDIATE statement)".