n my last article, “New PL/SQL Features in Oracle 8i: Part I“, I described some of the new features in Oracle 8i that extend the power, performance, and flexibility of PL/SQL, Oracle’s procedural extension to industry-standard SQL. This article covers some of the other new features: bulk binds, autonomous transactions, and new database triggers.

Bulk Binds
Oracle 8i introduces new PL/SQL FORALL and BULK COLLECT statements to support bulk binds.

The FORALL statement is specifically used for processing DML (INSERT, DELETE, and UPDATE) statements to improve performance by reducing the overhead of SQL processing. The PL/SQL interpreter executes all procedural statements. However, all SQL statements in the PL/SQL block are sent to the SQL engine, which parses and executes them. The PL/SQL-to-SQL context switch adds some overhead, which could become significant when SQL statements are nested in loops. For instance, if it’s repeated in a loop?say, 1,000,000 times?that could slow down code execution substantially.

Look at this code:

— Assume PL/SQL tables Invoice_Id_Tab, Invoice_Date_Tab,– and Invoice_Amount_Tab have been populated — with invoice information (Invoice ID, Date and Amount) — that needs to be inserted to the Invoice table.FOR nCount IN 1..10000LOOP   INSERT INTO Invoices (Invoice_Id, Invoice_Date, Invoice_Amount)      VALUES (Invoice_Id_Tab(nCount),              Invoice_Date_Tab(nCount),              Invoice_Amount_Tab(nCount));END LOOP;

Here 10,000 rows are created in the Invoice table and each INSERT is done individually, which results in 10,000 context switches between PL/SQL and SQL engines.

Oracle 8i eliminates all but one of these context switches by passing the entire PL/SQL table to the SQL engine in one step:

FORALL nCount IN 1..10000   INSERT INTO Invoices (Invoice_Id, Invoice_Date, Invoice_Amount)      VALUES (Invoice_Id_Tab(nCount),              Invoice_Date_Tab(nCount),              Invoice_Amount_Tab(nCount));

The FORALL statement has a structure similar to FOR LOOP with a range. However, it doesn’t contain an END LOOP statement and it cannot contain any statements other than the index, lower and upper bound, and actual SQL statement (which refers to the index). The range specified by lower and upper bounds (in my example, it’s 1 to 10,000) must be contiguous and all the elements within that range must exist, otherwise an ORA-22160 exception will be raised.

The FORALL clause is used for DML statements. The equivalent statement for a bulk fetch is the BULK COLLECT clause, which can be used as a part of SELECT INTO, FETCH INTO, or RETURNING INTO clauses:

SELECT Invoice_Id, Invoice_Date, Invoice_Amount   BULK COLLECT INTO Invoice_Id_Tab, Invoice_Date_Tab, Invoice_Amount_Tab   FROM Invoice;

The BULK COLLECT clause can be used for both explicit (FETCH INTO) and implicit (SELECT INTO) cursors. It fetches the data into the collection (PL/SQL table, varray) starting with element 1 and overwrites all consequent elements until it retrieves all the rows. If the collection is varray, it has to be declared large enough to accommodate all fetched rows, otherwise an ORA-22160 exception will be raised.

The bulk binds features allow users to increase the performance and reduce the overhead of SQL processing by operating on multiple rows in a single DML statement. The entire collection-not just one collection element at a time-is passed back and forth between the PL/SQL and SQL engines. According to Oracle, during internal benchmark tests there was a 30 percent performance improvement as a result of using these new features.

Autonomous Transactions
Because Oracle is a transactional database, every INSERT, DELETE, and UPDATE operation takes place in the context of a transaction, which is either committed to the database or rolled back as a whole. Prior to Oracle 8i, there was no way to commit individual a SQL operation that was separate from the whole transaction. Oracle 8i introduces a new compiler directive through the AUTONOMOUS_TRANSACTION pragma (a compiler directive), enabling PL/SQL program units to maintain their own transaction states.

An autonomous transaction starts within the context of another transaction, known as a parent transaction, but it is independent of it. This feature allows developers to handle transactions with more ease and finer granularity. Nested transactions can be committed or rolled back without affecting the parent one.

Here is an example of a Create_Invoice procedure with AUTONOMOUS_TRANSACTION pragma:

CREATE OR REPLACE PROCEDURE Create_Invoice   (Inv_Id INTEGER,     Inv_Amount NUMBER) IS   PRAGMA AUTONOMOUS_TRANSACTION;BEGIN   INSERT INTO Invoice      (Invoice_Id, Invoice_Date, Invoice_Amount)      VALUES      (Inv_Id, SYSDATE, Inv_Amount);   COMMIT;END;/

The next PL/SQL block is trying to create two new rows in the Invoice table: one directly via INSERT command, and one via the Create_Invoice stored procedure:

BEGIN   INSERT INTO Invoice      (Invoice_Id, Invoice_Date, Invoice_Amount)      VALUES      (1, SYSDATE, 25.50);   Create_Invoice(2, 30.20);   ROLLBACK;	END;/

As a result, the invoice with ID = 2 for $30.20 was created; the invoice with ID = 1 for $25.50 was not. Without the AUTONOMOUS_TRANSACTION pragma, both rows would have been committed to the database because the COMMIT command in the Create_Invoice procedure would have applied to the whole transaction.

New Database Triggers
Prior to Oracle 8i, database triggers could be applied to tables only. Essentially, they were table triggers. Oracle 8i introduces eight new database triggers, which extend beyond previous limitation. Table 1 lists these triggers.

Table 1. New Oracle8i Triggers
Trigger Event	Executes Before/After	Trigger Description
STARTUP	AFTER	Executes when the database is started
SHUTDOWN	BEFORE	Executes when the database is shut down
SERVERERROR	AFTER	Executes when a server-side error occurs
LOGON	AFTER	Executes when a session connects to the database
LOGOFF	BEFORE	Executes when a session disconnects from the database
CREATE	AFTER	Executes when a database object is created; could be created to apply to the schema or to the entire database
ALTER	AFTER	Executes when a database object is altered; could be created to apply to the schema or to the entire database
DROP	AFTER	Executes when a database object is dropped; could be created to apply to the schema or to the entire database

All eight triggers are executed implicitly, which is the same as triggers in previous Oracle versions. Once a database trigger is created and enabled, it executes upon a trigger event and no explicit calls are necessary.

New Oracle 8i database triggers are especially useful for DBAs because they provide coverage for the entire database. Now DBAs can move code from SQL scripts executed upon database startup to the STARTUP trigger. An object audit trail can be created using the CREATE, ALTER, and DROP triggers. STARTUP and SHUTDOWN triggers can record all users connected to the database with their log-on and log-off time.

Oracle 8i database triggers provide a new, convenient way of performing monitoring activities.