New PL/SQL Features in Oracle 8i: Part II

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



Executes when the database is started



Executes when the database is shut down



Executes when a server-side error occurs



Executes when a session connects to the database



Executes when a session disconnects from the database



Executes when a database object is created; could be created to apply to the schema or to the entire database



Executes when a database object is altered; could be created to apply to the schema or to the entire database



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.

Share the Post:
Share on facebook
Share on twitter
Share on linkedin


The Latest

your company's audio

4 Areas of Your Company Where Your Audio Really Matters

Your company probably relies on audio more than you realize. Whether you’re creating a spoken text message to a colleague or giving a speech, you want your audio to shine. Otherwise, you could cause avoidable friction points and potentially hurt your brand reputation. For example, let’s say you create a

chrome os developer mode

How to Turn on Chrome OS Developer Mode

Google’s Chrome OS is a popular operating system that is widely used on Chromebooks and other devices. While it is designed to be simple and user-friendly, there are times when users may want to access additional features and functionality. One way to do this is by turning on Chrome OS

homes in the real estate industry

Exploring the Latest Tech Trends Impacting the Real Estate Industry

The real estate industry is changing thanks to the newest technological advancements. These new developments — from blockchain and AI to virtual reality and 3D printing — are poised to change how we buy and sell homes. Real estate brokers, buyers, sellers, wholesale real estate professionals, fix and flippers, and beyond may