Differences in TimesTen: Exception Handling and Error Behavior

TimesTen PL/SQL Transaction and Rollback Behavior for Unhandled Exceptions

TimesTen PL/SQL differs from Oracle Database 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 Database rolls back to the beginning of the anonymous block. TimesTen does 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 fails because values must be unique, so an exception occurs and the program performs a rollback. Running this in TimesTen results in this:

oops:TT0907: Unique constraint (MYTABLE) violated at Rowid <BMUFVUAAABQAAAADjq>
 
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 indicates 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 Database, there is a rollback to the beginning of the PL/SQL block, so the results of the SELECT indicate execution of only the first insert:

ORA-00001: unique constraint (SYSTEM.SYS_C004423) violated
ORA-06512: at line 3
 
       NUM
----------
         1

Note:

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 can 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 is rolled back.

TimesTen Error Messages and SQL Codes

Given the same error condition, TimesTen does not guarantee that the error message returned by TimesTen is the same as the message returned by Oracle Database, although the SQL code is the same. Therefore, the information returned by the SQLERRM function may be different, but that returned by the SQLCODE function is the same.

For further information:

The example in Using the RAISE Statement uses SQLERRM and SQLCODE.
Refer to Errors and Warnings 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 information.

Warnings Not Visible in PL/SQL

Oracle Database does not have the concept of runtime warnings, so Oracle Database PL/SQL does not support warnings. TimesTen does have the concept of warnings, but because the TimesTen PL/SQL implementation is based on the Oracle Database 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 would not see the warning.

Unsupported Predefined Errors

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 Database Error Number	SQLCODE	Description
`LOGIN_DENIED`	`ORA-01017`	-1017	User name or password is invalid.
`NOT_LOGGED_ON`	`ORA-01012`	-1012	Program issued a database call without being connected to the database.
`SELF_IS_NULL`	`ORA-30625`	-30625	Program attempted to invoke a `MEMBER` method, but the object was not initialized.
`TIMEOUT_ON_RESOURCE`	`ORA-00051`	-51	Timeout occurred while the database was waiting for a resource.

Possibility of Runtime Errors After Clean Compile (Use of Oracle Database SQL Parser)

The TimesTen PL/SQL implementation uses the Oracle Database 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 Database syntax or built-in procedures that are not supported by TimesTen, the issue is not discovered during compilation. A runtime error occurs during program execution, however.

Use of TimesTen Expressions at Runtime

TimesTen SQL includes several constructs that are not present in Oracle Database 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 avoids compilation errors.

For lists of TimesTen-specific SQL and expressions, see Compatibility Between TimesTen and Oracle Databases in Oracle TimesTen In-Memory Database Cache Guide.

For more information about EXECUTE IMMEDIATE, refer to Dynamic SQL in PL/SQL (EXECUTE IMMEDIATE Statement).