Oracle8i New Features

Oracle8i is for many developers still a relatively new release of Oracle, so we thought it would be useful to describe some of the most important Oracle8i PL/SQL features covered in this book. They are summarized in the following sections, and covered more thoroughly in the indicated chapters.

Autonomous transactions

One long-standing request from PL/SQL developers has been the ability to execute and then save or cancel certain Data Manipulation Language (DML)statements (INSERT, UPDATE, DELETE) without affecting the overall session's transaction. You can now do this with autonomous transactions.

Where would you find autonomous transactions useful in yourapplications? Here are some ideas:

Logging mechanism

This is the classic example of the need for an autonomous transaction. You need to log error information in a database table, but don't want that log entry to be a part of the logical transaction.

Reusable application components

You are building an Internet application. You want to combine components from many different vendors and layers, and they need to interact in certain well-defined ways. If when one component commits, it affects all other aspects of your application, it will not function well in this environment. Autonomous transactions solve this problem.

When you define a PL/SQLblock (anonymous block, procedure, function, packaged procedure, packaged function, or database trigger) as an autonomous transaction, you isolate the DML in that block from the rest of your session. That block becomes an independent transaction that is started by another transaction, referred to as the main transaction. Within the autonomous transaction block, the main transaction is suspended. You perform your SQL operations, commit or roll back those operations, and then resume the main transaction.

There isn't much involved in defining a PL/SQL block as an autonomous transaction. You simply include the following statement in your declaration section:

PRAGMA AUTONOMOUS_TRANSACTION;

Here is a very simple logging mechanism that relies on the autonomous transaction feature to save changes to the log without affecting the rest of the session's transaction:

PROCEDURE write_log ( code IN INTEGER, text IN VARCHAR2)IS PRAGMA AUTONOMOUS_TRANSACTION;BEGIN INSERT INTO log VALUES ( code, text, USER, SYSDATE ); COMMIT:END;

Of course, there are all sorts of rules and some restrictions to be aware of. See Chapter 13 for all the details.