4 Errors and Exception Handling

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:

Understanding exceptions
Trapping exceptions
Showing errors in ttIsql
Differences in TimesTen: exception handing and error behavior

Understanding exceptions

This section provides an overview of exceptions in PL/SQL programming, covering the following topics:

About exceptions
Exception types

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

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

Trapping exceptions

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

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

Table 4-1 Predefined exceptions

Exception name	Oracle 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 were 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	A program attempted to open an already opened cursor.
`DUP_VAL_ON_INDEX`	`ORA-00001`	-1	A program attempted to insert duplicate values in a column that is constrained by a unique index.
`INVALID_CURSOR`	`ORA-01001`	-1001	Illegal 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 legal 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 value `ROWID`.
`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.

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.

Trap ping 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.

Usi ng the RAISE statement

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

Us ing 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.

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.

Sho wing errors in ttIsql

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

Differences in TimesTen: exception handing and error behavior

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
TimesTen error messages and SQL codes
Warnings not visible in PL/SQL
Unsupported predefined errors
Possibility of runtime errors after clean compile (use of Oracle SQL parser)
Use of TimesTen expressions at runtime

TimesTen PL/SQL tran saction 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 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.

TimesTen er ror 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 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.

W arnings not visible in PL/SQL

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.

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 error number	SQLCODE	Description
`LOGIN_DENIED`	`ORA-01017`	-1017	Invalid user name and password.
`NOT_LOGGED_ON`	`ORA-01012`	-1012	A program issued a database call without being connected to the database.
`SELF_IS_NULL`	`ORA-30625`	-30625	A program attempted to invoke a `MEMBER` method, but the object was not initialized.
`TIMEOUT_ON_RESOURCE`	`ORA-00051`	-51	A timeout occurred while the database was waiting for a resource.

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

Use of TimesTen expressions at runtime

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

4 Errors and Exception Handling

Understanding exceptions

About exceptions

Exception types

Trapping exceptions

Trapping predefined TimesTen errors

Trapping user-defined exceptions

Using the RAISE statement

Using the RAISE_APPLICATION_ERROR procedure

Showing errors in ttIsql