Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


advertisement
 

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

Covers new features introduced in the 8i version of Oracle, including bulk binds, autonomous transactions, and new database triggers.


advertisement
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..10000 LOOP 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.



   
Boris Milrud has 10 years of experience as a software developer. He is a Senior Database Engineer with Callidus Software, Inc. located in San Jose CA. He is specializing in all aspects of Oracle database software development including database design, programming, optimization and tuning.
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date