1.3.1 Autonomous Transactions ( Chapter 2, Choose Your Transaction! )

One long-standing request from PL/SQL developers has been to have 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 your applications? 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.

Commits or rollbacks in your database triggers

Finally! If you define a trigger as an autonomous transaction, then you can commit and/or roll back in that code.

Retry counter

Autonomous transactions can help you keep track of how many times a user tries to connect to a database or get access to a resource (you'll reject access after a certain number of attempts).

Software usage meter

A similar type of situation is when you want to track how often a program is called during an application session. In fact, autonomous transactions are helpful in meeting any application requirement that calls for persistently storing a state (how many times did Joe try to update the salary column?).

Reusable application components

You are building an Internet application. You want to combine components from many different vendors and layers. 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.

When you define a PL/SQL block (anonymous block, procedure, function, packaged procedure, packaged function, 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 relying 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 2 , for all the details.

1.3.2 Invoker Rights ( Chapter 3, Invoker Rights: Your Schema or Mine? )

Back in the "old days" of Oracle7 and Oracle 8.0, whenever you executed a stored program, it executed under the authority of the owner of that program. This was not a big deal if your entire application -- code, data, and users -- worked out of the same Oracle account. That scenario probably fit about 0.5% of all Oracle shops. It proved to be a real pain in the neck for the other 99.5%, though, because usually code was stored in one schema and then shared through GRANT EXECUTE statements with other users (directly or through roles).

For one thing, that centralized, stored code would not automatically apply the privileges of a user (also known as an invoker ) to the code's objects. The user might not have had DELETE privileges on a table, but the stored code did, so delete away! Now, in some circumstances, that is just how you wanted it to work. In others, particularly when you were executing programs relying on the DBMS_SQL (dynamic SQL) package, awesome complications could ensue.

In Oracle 8.1, PL/SQL has now been enhanced so that at the time of compilation, you can decide whether a program (or all programs in a package) should run under the authority of the definer (the only choice in Oracle 8.0 and below) or of the invoker of that program.

The syntax to support this feature is simple enough. Here is a generic "run DDL" engine that relies on the new native dynamic SQL statement EXECUTE IMMEDIATE:

CREATE OR REPLACE PROCEDURE runddl (ddl_in in VARCHAR2)
   AUTHID CURRENT_USER 
IS
BEGIN
   EXECUTE IMMEDIATE ddl_in;
END;
/

The AUTHID CURRENT_USER clause before the IS keyword indicates that when runddl executes, it should run under the authority of the invoker, or "current user," not the authority of the definer.

1.3.3 Native Dynamic SQL ( Chapter 4, Native Dynamic SQL in Oracle8i )

Ever since Oracle 7.1, we PL/SQL developers have been able to use the built-in DBMS_SQL package to execute dynamic SQL and PL/SQL. This means that at runtime you can construct the query, a DELETE or CREATE TABLE, or even a PL/SQL block, as a string -- and then execute it. Dynamic SQL comes in extremely handy when you are building ad hoc query systems, when you need to execute Data Definition Language (DDL) inside PL/SQL, and just generally when you don't know in advance exactly what you need to do or what the user will want to do. Dynamic SQL is a frequent requirement in Web-based applications.

But there are some problems with DBMS_SQL:

• It is a very complicated package.

• It has a number of restrictions (such as not recognizing and being able to work with new Oracle8 datatypes).

• It is relatively slow.

So our dear friends at PL/SQL Central in Redwood Shores took pity on us all and reimplemented dynamic SQL directly in the PL/SQL language itself. This is called native dynamic SQL (NDS).

In my latest book on PL/SQL, Oracle Built-in Packages (O'Reilly & Associates, coauthored with John Beresniewicz and Charles Dye), I spent about 100 pages explaining dynamic SQL and the DBMS_SQL package. While NDS makes it much easier to get your dynamic job done, there is still a whole lot to say on this subject. Let's just compare a DBMS_SQL and NDS implementation of a program that displays all the employees for the specified and dynamic WHERE clause.

The DBMS_SQL implementation:

CREATE OR REPLACE PROCEDURE showemps (
   where_in IN VARCHAR2 := NULL)
IS
   cur INTEGER := DBMS_SQL.OPEN_CURSOR;
   rec employee%ROWTYPE;
   fdbk INTEGER;
BEGIN
   DBMS_SQL.PARSE
     (cur, 
      'SELECT employee_id, last_name 
         FROM employee 
        WHERE ' || NVL (where_in, '1=1'),
      DBMS_SQL.NATIVE);
   DBMS_SQL.DEFINE_COLUMN (cur, 1, 1);
   DBMS_SQL.DEFINE_COLUMN (cur, 2, user, 30);
   fdbk := DBMS_SQL.EXECUTE (cur);
   LOOP
      /* Fetch next row. Exit when done. */
      EXIT WHEN DBMS_SQL.FETCH_ROWS (cur) = 0;
      DBMS_SQL.COLUMN_VALUE (cur, 1, rec.employee_id);
      DBMS_SQL.COLUMN_VALUE (cur, 2, rec.last_name);
      DBMS_OUTPUT.PUT_LINE (
         TO_CHAR (rec.employee_id) || '=' || 
         rec.last_name);
   END LOOP;
   DBMS_SQL.CLOSE_CURSOR (cur);
END;
/

And now the NDS implementation:

CREATE OR REPLACE PROCEDURE showemps (
   where_in IN VARCHAR2 := NULL)
IS
   TYPE cv_typ IS REF CURSOR;
   cv cv_typ;
   v_id employee.employee_id%TYPE;
   v_nm employee.last_name%TYPE;
BEGIN
   OPEN cv FOR 
      'SELECT employee_id, last_name 
         FROM employee 
        WHERE ' || NVL (where_in, '1=1');
   LOOP
      FETCH cv INTO v_id, v_nm;
      EXIT WHEN cv%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE (TO_CHAR (v_id) || '=' || v_nm);
   END LOOP;
   CLOSE cv;
END;
/

I know which one I would prefer. I'll be looking at native dynamic SQL in much more detail in Chapter 4 . And if you are an expert at DBMS_SQL and feel tears coming to your eyes about all that wasted intellectual property, take heart in these factoids:

• No one has invested more time and typed words in DBMS_SQL than yours truly.

• DBMS_SQL is not completely obsolete. There are still times when you will want to use it instead of NDS, and there are things you can do with DBMS_SQL that are not yet possible with NDS.

1.3.4 Bulk Binds and Collects ( Chapter 5, Bulking Up with PL/SQL 8.1 )

One of the major priorities of the PL/SQL development team is to speed up the performance of their language. This effort cannot come a moment too soon. We developers have been complaining about runtime performance for years, and finally the developers are responding (though, to be brutally honest, it seems to me that the intensive tuning steps taken in Oracle 8.0 were motivated at least partly by the need to make PL/SQL fast enough to support object types).

One area of improvement concerns the execution of "bulk" DML inside PL/SQL. Consider, for example, the following code that deletes each employee identified by the employee number found in the nested table list:

CREATE TYPE empnos_list_t IS VARRARY(100) OF NUMBER;
CREATE OR REPLACE del_emps (list_in IN empnos_list_t)
IS
BEGIN
   FOR listnum IN list_in.FIRST.. list_in.LAST 
   LOOP
      DELETE FROM emp WHERE empno = list_in (listnum);
   END LOOP;
END;

Easy to write, easy to read. But what about performance? Whenever this program issues its DELETE, a context switch takes place from PL/SQL to SQL to execute that command. If there are 100 elements in the list, there are 100 switches, with corresponding performance degradation.

Recognizing this common requirement and its overhead, Oracle now offers a bulk bind variation on the FOR loop -- the FORALL statement. With this statement, you can recode the del_emps procedure as follows:

CREATE OR REPLACE del_emps (list_in IN empnos_list_t)
IS
BEGIN
   FORALL listnum IN list_in.FIRST.. list_in.LAST 
   LOOP
      DELETE FROM emp WHERE empno = list_in (listnum);
END;

Now there will be just one context switch: all of the DELETE operations will be bundled into a single bulk operation and passed to the SQL layer together.

In addition to the FORALL bulk DML operator, Oracle 8.1 also offers the BULK COLLECT variation on the INTO clause of an implicit query. This operation allows you to retrieve multiple rows in a single context switch.

1.3.5 New Trigger Capabilities ( Chapter 6, New Trigger Features in Oracle8i )

Oracle8 i expands significantly the use of triggers to administer a database and "publish" information about events taking place within the database. By employing database triggers on the newly defined system events and using Oracle Advanced Queuing within those triggers, you can take advantage of the publish/subscribe capabilities of Oracle8 i .

The database event publication feature allows applications to subscribe to database events just as they subscribe to messages from other applications. The trigger syntax is extended to support system and other data events on database or schema. Trigger syntax also supports a CALL to a procedure as the trigger body.

You can now enable the publication of (i.e., define a programmatic trigger on) the following actions:

• DML statements (DELETE, INSERT, and UPDATE)

• DDL events (e.g., CREATE, DROP, and ALTER)

• Database events (SERVERERROR, LOGON, LOGOFF, STARTUP, and SHUTDOWN)

These are the new trigger features available in Oracle8 i :

Triggers on nested table columns

The CAST. . .MULTISET operation allows you to trigger activity when only an attribute in a nested table column is modified.

Database-level event triggers

You can now define triggers to respond to such system events as LOGON, DATABASE SHUTDOWN, and even SERVERERROR.

Schema-level event triggers

You can now define triggers to respond to such user- or schema-level events as CREATE, DROP, and ALTER.

1.3.6 New and Enhanced Built-in Packages ( Chapter 7, New and Enhanced Built-in Packages in Oracle8i )

Oracle has added a number of new built-in packages in Oracle8 i . Many of them are for fairly specialized purposes, such as the replication facility or online analytical processing (OLAP)/data warehouse optimization, but a number of the packages, including those listed here, will come in very handy for database administrators (DBAs) and PL/SQL developers:

DBMS_JAVA

Gives you the ability to modify the behavior of the Aurora Java Virtual Machine (JVM) in Oracle. You can enable output (meaning that System.out.println will act like DBMS_OUTPUT.PUT_LINE), set compiler and debugger options, and more.

DBMS_PROFILER

Accesses performance and code coverage analysis of your PL/SQL application.

DBMS_RLS

Offers an interface to the fine-grained access control administrative features of Oracle8 i ; it is only available with the Enterprise Edition.

DBMS_TRACE

Allows PL/SQL developers to trace the execution of stored PL/SQL functions, procedures, and exceptions.

UTL_COLL

Allows PL/SQL programs to use collection locators in order to perform queries and updates.

Oracle has also enhanced a number of packages, including DBMS_UTILITY, DBMS_AQ, and DBMS_LOB.

Chapter 7 , introduces you to a subset of the capabilities of these packages. Comprehensive, reference-oriented coverage of these packages will be included in the second edition of Oracle Built-in Packages (O'Reilly & Associates, expected in 2000).

1.3.7 Fine-Grained Access Control ( Chapter 8, Deploying Fine-Grained Access Control )

Fine-grained access control (FGAC) is a new feature in Oracle8 i that allows you to implement security policies with functions and then use those security policies to implement row-level security on tables or views. The database server automatically enforces security policies, no matter how the data is accessed -- through SQL*Plus or the Internet, as an ad hoc query, or as an update processed through an Oracle Forms application.

What, you might ask, is a security policy? Consider the following very simple scenario (I'll expand upon this scenario in Chapter 8 ). Suppose that I have tables of hospital patients and their doctors defined as follows:

CREATE TABLE patient ( 
   patient_id NUMBER,  
   name VARCHAR2(100), 
   dob DATE,           
   doctor_id INTEGER
   ); 

CREATE TABLE doctor (
   doctor_id NUMBER,
   name VARCHAR2(100)
 );

Now suppose that I want to let a doctor see only her patients when she issues a query against the table. More than that, I don't want to let doctors modify patient records unless they are that doctor's patients. You could achieve much of what is needed through the creation of a set of views, but wouldn't it be grand if you could just let any doctor connect to her schema in Oracle and say:

SELECT * FROM patient;  

and see only her patients? In other words, hide all the rules needed to enforce the appropriate privacy and security rules (the policy ) so that the policy is transparent to users of the data structures. That's what the fine-grained access control feature does for you!

With Oracle8 i 's fine-grained access control, also known as row-level security , you can apply different policies to SELECT, INSERT, UPDATE, and DELETE, and use security policies only where you need them (for example, on salary information). You can also design and enforce more than one policy for a table, and can even construct layers of policies (one policy building on top of an existing policy) to handle complex situations.

1.3.8 Calling Java from PL/SQL ( Chapter 9, Calling Java from PL/SQL )

Java is a very powerful language, much more robust in many ways than PL/SQL. Java also offers hundreds of classes that provide clean, easy-to-use application programming interfaces (APIs) to a wide range of functionality.

In Oracle8 i , Oracle includes a new product called JServer. JServer consists of the following elements:

• Oracle's Java Virtual Machine, called Aurora, the supporting runtime environment, and Java class libraries

• Tight integration with PL/SQL and Oracle relational database management system (RDBMS) functionality

• An Object Request Broker (the Aurora/ORB) and Enterprise JavaBeans(TM) (EJB)

• The JServer Accelerator (native compiler); available in the 8.1.6 Enterprise Edition only

The Aurora JVM executes Java methods (also known as Java stored procedures, or JSPs) and classes if they are stored in the database itself. This means that even if you are a full-time Oracle PL/SQL developer, you can take advantage of the wonderful world of Java to build your applications.

Java in the Oracle database is a big topic; Java programming all by itself is an even bigger topic. Complete treatment of either is outside the scope of this book. Chapter 9 , focuses on the exciting new feature of Oracle8 i that allows a developer to call Java stored procedures from within PL/SQL.

1.3.9 Miscellaneous New Features and Performance Improvements ( Chapter 10, More Goodies for Oracle8i PL/SQL Developers )

By the time you get to Chapter 10 , you will have learned about a wide range of significant new capabilities in PL/SQL. Yet there is still more! This chapter covers some other features that improve either the performance or usability of PL/SQL in Oracle8 i . I'll also review transparent improvements, that is, changes to the language that improve the performance or behavior of your PL/SQL-based applications without necessitating any modifications to your code.

Major topics include the following:

The NOCOPY compiler hint

You can avoid the overhead of copying IN OUT parameter values with this enhancement. When you are working with large collections and records, NOCOPY can have a noticeable impact on program performance.

Calling PL/SQL functions from SQL

Oracle8 i offers some big relief for PL/SQL developers when it comes to calling their own functions: you no longer have to use the RESTRICT_REFERENCES pragma! Oracle8 i also offers two new keywords, DETERMINISTIC and PARALLEL_ENABLE, to help you integrate your PL/SQL, C, and Java code into all aspects of your database.

SQL99 compliance

Oracle8 i adds or expands the TRIM and CAST operators to better support the SQL99 standard.

SQL operations on collections

Oracle8 i makes it even easier to integrate PL/SQL (transient) collections into SQL statements.