4 Errors and Exception Handling

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 more than one row, 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.

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.

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".

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

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

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.

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.

TimesTen PL/SQL transaction and rollback behavior for unhandled exceptions

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, with the SELECT 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 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 will indicate execution of only the first INSERT:

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

TimesTen error messages and SQL codes

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, 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, "Using RAISE statement to trap user-defined exception" 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 (as well as Oracle-specific) information.

Warnings not visible in PL/SQL

Oracle Database does not have the concept of runtime warnings, so Oracle PL/SQL does not support the concept of runtime 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.

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.

Possibility of runtime errors after clean compile (use of Oracle SQL parser)

The TimesTen PL/SQL implementation uses the Oracle SQL parser in compiling PL/SQL programs. As a result, if your program uses Oracle syntax or Oracle built-ins that are not supported by TimesTen, the issue will not be discovered during compilation. A runtime error would occur during program execution, however.