Oracle PL/SQL Programming: Guide to Oracle8i Featu

Content preview·Buy reprint rights for this chapter

After long delays, Oracle8i Release 8.1 went into production in 1999. Oracle8i, the "Internet database," is packed with an astounding and intimidating array of powerful new features—and I don't even work for Oracle any more! Hey, I don't have to use exclamation marks to describe all the great things you can do with Oracle8i Release 8.1!

Whew. Got that out of my system. Do you ever have trouble separating the marketing from the reality with Oracle Corporation? The name of their latest release brings that issue to the fore like never before. "Oracle8i Release 8.1"—what does all that mean? Here is how I see it: "Oracle8i " —that's the marketing piece touting the first database for the Internet. "Release 8.1"—now they're talking my language, the actual database version. I started with Oracle 5.1 and have been steadily pleased with the improvements through Oracle6 (quickly, please) and then Oracle7. Oracle 8.0 was a .0 release and, as a result, many, many organizations around the world paid very little attention to it. Now, finally, we have Oracle 8.1 and I expect that many organizations will move relatively quickly to this release level.

What do you get with Oracle 8.1? The list is very long and very impressive ("more than 150 new features," says Oracle). Here are some of the features I have heard about:

The Aurora Java™ Virtual Machine ( JVM) right inside the database
Support for Java stored procedures (JSPs)
JDeveloper, a Java™ Integrated Development Environment
i FS, the Internet File System (not currently available at the time of publication)
Support for the Linux operating system

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

What do you get with Oracle 8.1? The list is very long and very impressive ("more than 150 new features," says Oracle). Here are some of the features I have heard about:

The Aurora Java™ Virtual Machine ( JVM) right inside the database
Support for Java stored procedures (JSPs)
JDeveloper, a Java™ Integrated Development Environment
i FS, the Internet File System (not currently available at the time of publication)
Support for the Linux operating system
inter Media
Support for SQLJ and JDBC™ ( Java Database Connectivity)
WebDB
Autonomous transactions
Invoker rights model
Many performance improvements

Throughout this book, Oracle 8.1 refers to Oracle8i Release 8.1, and PL/SQL 8.1 refers to the Oracle8i

The Oracle8i documentation has a section titled "Getting to Know Oracle8i"; the table of contents for new features is 259 lines long! Is that more than mere mortals can deal with? It sure is more than I can deal with. So I am going to stick with what I know best: PL/SQL. This book focuses almost exclusively on new Oracle8i features that have a direct impact on (or are implemented by) the PL/SQL language. As you will see, even if the focus is constrained to PL/SQL, there are many, many new techniques and technologies to learn, absorb, and then leverage in application environments.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Before I dive into my feature roundups, allow me to reflect for a moment on the PL/SQL language and its future in the Oracle environment. Why would I feel the need to do this? I can answer with one word—Java.

Contrary to popular belief, I do not hate or fear Java. I hold no animosity for a language that has at least the potential to unseat PL/SQL as the dominant (used to be only) programming language inside the Oracle database. I have begun to study Java and have found that it's very different from PL/SQL and much more powerful.

There is no doubt that we will all need to be proficient enough with both languages to be able to:

Decide which language is best used to solve a particular problem
Call Java stored procedures from PL/SQL, and vice versa

So yes, Oracle supports interoperability between these two languages, and we need to be ambidextrous when it comes to "left brain" PL/SQL and "right brain" Java.

The big question or rumor that has floated around the Oracle world lately, though, is a more troubling one: will Oracle simply abandon PL/SQL for Java? Not only would that put me out of business, it would also cause tremendous upheaval in the Oracle customer world. It is simply not going to happen, and the best way to demonstrate that fact is to see the forward motion in the PL/SQL language.

When object technology was first introduced in the Oracle database and in PL/SQL (in Oracle 8.0), a debate raged within Oracle headquarters: should PL/SQL become a full-fledged object-oriented language? Should it remain focused on what it does best? Now with the incorporation of Java into the Oracle database, this debate has been resolved. PL/SQL is the premier database programming language (specific to Oracle but superior to the others, such as Informix's 4GL and Sybase/Microsoft's Transact-SQL), as demonstrated by the adoption of many PL/SQL features and syntax into the ANSI standards. Oracle will focus its energies on maintaining that position.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Even if I stick to the narrow course of exploring only those PL/SQL-related new features of Oracle8i, I can still find lots to talk about. This section previews the chapters of the book and introduces you to the main PL/SQL enhancements in this release of Oracle.

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?).

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

One of the more exciting new features of Oracle8i Release 8.1's version of PL/SQL is a capability called autonomous transactions. In this chapter, I'll discuss several ways you can put this feature to use in your application development environment.

PL/SQL is tightly integrated with the Oracle RDBMS; that is, after all, why it is called "PL/SQL"—procedural language extensions to SQL. When you perform operations in the database, you do so within the context of a transaction, a series of one or more SQL statements that perform a logical unit of work. A transaction can have associated with it a variety of locks on resources (rows of data, program units, etc.). These locks define the context of the transaction; the context also contains the actual data.

To appreciate transactions in Oracle, consider the "ACID" principle: a transaction has atomicity, consistency, isolation, and durability, which are defined as follows:

Atomic: A transaction's changes to a state are atomic: either they all happen or none happens.
Consistent: A transaction is a correct transformation of state. The actions taken as a group do not violate any integrity constraints associated with that state.
Isolated: Even though many transactions may be executing concurrently, from any given transaction's point of view, other transactions appear to have executed before or after its execution.
Durable: Once a transaction completes successfully, the changes to the state are made permanent, and they survive any subsequent failures.

A transaction can either be saved by performing a COMMIT or erased by requesting a ROLLBACK. In either case, the affected locks on resources are released (a ROLLBACK TO might only release some of the locks). The session can then start a new transaction.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

PL/SQL is tightly integrated with the Oracle RDBMS; that is, after all, why it is called "PL/SQL"—procedural language extensions to SQL. When you perform operations in the database, you do so within the context of a transaction, a series of one or more SQL statements that perform a logical unit of work. A transaction can have associated with it a variety of locks on resources (rows of data, program units, etc.). These locks define the context of the transaction; the context also contains the actual data.

To appreciate transactions in Oracle, consider the "ACID" principle: a transaction has atomicity, consistency, isolation, and durability, which are defined as follows:

Atomic: A transaction's changes to a state are atomic: either they all happen or none happens.
Consistent: A transaction is a correct transformation of state. The actions taken as a group do not violate any integrity constraints associated with that state.
Isolated: Even though many transactions may be executing concurrently, from any given transaction's point of view, other transactions appear to have executed before or after its execution.
Durable: Once a transaction completes successfully, the changes to the state are made permanent, and they survive any subsequent failures.

A transaction can either be saved by performing a COMMIT or erased by requesting a ROLLBACK. In either case, the affected locks on resources are released (a ROLLBACK TO might only release some of the locks). The session can then start a new transaction.

Before the release of PL/SQL 8.1, each Oracle session could have at most one active transaction at a given time. In other words, any and all changes made in your session had to be either saved or erased in their entirety. This restriction has long been considered a drawback in the PL/SQL world. Developers have requested the ability to execute and save or cancel certain DML statements (INSERT, UPDATE, DELETE) without affecting the overall session's transaction.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

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;

The pragma instructs the PL/SQL compiler to establish a PL/SQL block as autonomous or independent. For the purposes of the autonomous transaction, a PL/SQL block can be any of the following:

Top-level (but not nested) anonymous PL/SQL blocks
Functions and procedures, defined either in a package or as standalone programs
Methods (functions and procedures) of a SQL object type
Database triggers

You can put the autonomous transaction pragma anywhere in the declaration section of your PL/SQL block. You would probably be best off, however, placing it before any data structure declarations. That way, anyone reading your code will immediately identify the program as an autonomous transaction.

This pragma is the only syntax change made to PL/SQL to support autonomous transactions. COMMIT, ROLLBACK, the DML statements—all the rest is as it was before. However, these statements have a different scope of impact and visibility (discussed later in this chapter) when executed within an autonomous transaction, and you will have to include a COMMIT or ROLLBACK in your program.

Let's look at a simple example. Suppose you are responsible for building a database to keep track of war criminals for the International Court of Justice. You create a package called wcpkg to keep track of alleged war criminals. One of the programs in the package registers a new criminal. You always want that register program to save its changes, even if the calling program hasn't yet issued a COMMIT. These characters are, after all, fairly slippery and you don't want them to get away.

The package specification holds no surprises; the transaction type is not evident here:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Where would you find autonomous transactions useful in your applications? First, let's reinforce the general principle: you will want to define your program module as an autonomous transaction whenever you want to isolate the changes made in that module from the caller's transaction context.

Here are some specific ideas:

Logging mechanism: On the one hand, you need to log an error to your database log table. On the other hand, you need to roll back your core transaction because of the error. And you don't want to roll back over other log entries. What's a person to do? Go autonomous!
Commits and rollbacks in your database triggers: If you define a trigger as an autonomous transaction, then you can commit and/or roll back in that code. Developers have been asking for this capability for a long time.
Retry counter: Suppose that you want to let a user try to get access to a resource N times before an outright rejection; you also want to keep track of attempts between connections to the database. This persistence requires a COMMIT, but one that should remain independent of the transaction.
Software usage meter: You want to keep track of how often a program is called during an application session. This information is not dependent on, and cannot affect, the transaction being processed in the application.
Reusable application components: This usage goes to the heart of the value of autonomous transactions. As we move more thoroughly into the dispersed, multilayered world of the Internet, it becomes ever more important to be able to offer standalone units of work (also known as

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

While it is certainly very easy to add the autonomous transaction pragma to your code, there are some rules and restrictions on the use of this feature. You can only make a top-level anonymous block an autonomous transaction. This will work:

DECLARE
   PRAGMA AUTONOMOUS_TRANSACTION;
   myempno NUMBER;
BEGIN
   INSERT INTO emp VALUES (myempno, ...);
   COMMIT;
END;
/

whereas this construction:

DECLARE
   myempno NUMBER;
BEGIN
   DECLARE
      PRAGMA AUTONOMOUS_TRANSACTION;
   BEGIN  
      INSERT INTO emp VALUES (myempno, ...);
      COMMIT;
   END;
END;
/

results in this error:

PLS-00710: PRAGMA AUTONOMOUS_TRANSACTION cannot be declared here

Just to expand your vision of what is possible, you can now use COMMIT and ROLLBACK inside your database triggers. These actions will not affect the transaction that caused the database trigger to fire, of course. See , later in this chapter, for more details on what you can accomplish.

If an autonomous transaction attempts to access a resource held by the main transaction (which has been suspended until the autonomous routine exits), you can cause a deadlock to occur in your program. Here is a simple example to demonstrate the problem. I create a procedure to perform an update, and then call it after having already updated all rows:

/* Filename on companion disk: autondlock.sql*/
CREATE OR REPLACE PROCEDURE 
   update_salary (dept_in IN NUMBER)
IS
   PRAGMA AUTONOMOUS_TRANSACTION;
   
   CURSOR myemps IS
      SELECT empno FROM emp
       WHERE deptno = dept_in
         FOR UPDATE NOWAIT;
BEGIN
   FOR rec IN myemps
   LOOP
      UPDATE emp SET sal = sal * 2 
       WHERE empno = rec.empno;
   END LOOP;
   COMMIT;
END;
/
   
BEGIN
   UPDATE emp SET sal = sal * 2;
   update_salary (10);
END;
/

The results are not pretty:

ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

You cannot mark all subprograms in a package (or all methods in an object type) as autonomous with a single PRAGMA declaration. You must indicate autonomous transactions explicitly in each program. For example, the following package specification is invalid:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

This section provides some complete examples of where you might use autonomous transactions in your applications.

A very common requirement in applications is to keep a log of errors that occur during transaction processing. The most convenient repository for this log is a database table; with a table, all the information is retained in the database and you can use SQL to retrieve and analyze the log.

One problem with a database table log, however, is that entries in the log become a part of your transaction. If you perform (or have performed to you) a ROLLBACK, you can easily erase your log. How frustrating! Now, it is true that you can get fancy and use savepoints to preserve your log entries while cleaning up your transaction, but that approach is not only fancy, it is complicated. With autonomous transactions, however, logging becomes simpler, more manageable, and less error prone.

Suppose I have a log table defined as follows:

/* Filename on companion disk: log81.pkg */
CREATE TABLE log81tab (
    code INTEGER,
    text VARCHAR2(4000),
    created_on DATE,
    created_by VARCHAR2(100),
    changed_on DATE,
    changed_by VARCHAR2(100),
    machine VARCHAR2(100),
    program VARCHAR2(100)
    );

I can use it to store errors (SQLCODE and SQLERRM) that have occurred, or even use it for non-error-related logging. The machine and program columns record information available from the virtual V$SESSION table, as you will see.

So I have my table. Now, how should I write to my log? Here's what you should not do:

EXCEPTION
   WHEN OTHERS
   THEN
      v_code := SQLCODE;
      v_msg := SQLERRM;
      INSERT INTO log81tab VALUES (
         v_code, v_msg, SYSDATE, USER, SYSDATE, USER, NULL, NULL);
END;

In other words, never expose your underlying logging mechanism by explicitly inserting into it in your exception sections and other locations. Instead, you should build a layer of code around the table (this is known as

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Invoker rights refers to a new model for resolving references to database elements (tables, views, objects, programs) in a PL/SQL program unit.

Back in the "old days" of Oracle7 and Oracle 8.0 (those days, of course, in which most of us still spend our time), 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, a scenario that probably covers about 0.5% of all Oracle shops. It proved to be a real pain in the neck for the other 99.5%, though, because code was usually 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 other situations, 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 earlier) or of the invoker of that program.

Before exploring the new invoker rights feature of PL/SQL, let's review the definer rights model. You need to understand the nuances of both models, because most PL/SQL applications will very likely rely on a combination of those models.

Before a PL/SQL program can be executed from within a database instance, it must be compiled and stored in the database itself. Thus, program units are always stored within a specific schema or Oracle account. A program can reference other PL/SQL programs, database tables, and so on. These are called

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Before exploring the new invoker rights feature of PL/SQL, let's review the definer rights model. You need to understand the nuances of both models, because most PL/SQL applications will very likely rely on a combination of those models.

Before a PL/SQL program can be executed from within a database instance, it must be compiled and stored in the database itself. Thus, program units are always stored within a specific schema or Oracle account. A program can reference other PL/SQL programs, database tables, and so on. These are called external references, since these database elements are not defined within the current program unit.

With the definer rights model, you should keep the following rules in mind:

Any external reference in a program unit is resolved at compile time using the directly granted privileges of the schema in which the program unit is compiled.
Database roles are ignored completely at compile time.
Whenever you run a program compiled with the definer rights model (the default), it executes under the authority of the schema that owns the program.
Although direct grants are needed to compile a program, you can rely on grants of EXECUTE authority to give other schemas the ability to run your program.

shows how you can use the definer rights model to control access to underlying data objects. All the order entry data is stored in the OEData schema. All the order entry code is defined in the OECode schema. OECode has been granted the direct privileges necessary to compile the Order_Mgt package, which allows you to both place and cancel orders.

Figure 3.1: Controlling access to data with the definer rights model

To make sure that the orders table is updated properly, no direct access (either via roles or privileges) is allowed to that table through any schema other than OECode. Suppose, for example, that the Sam_Sales schema needs to run through all the outstanding orders and close out old ones. Sam will

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

To help developers get around the obstacles raised by the definer rights model, Oracle 8.1 offers an alternative: the invoker rights model. With this approach, all external references in a PL/SQL program unit are resolved according to the directly-granted privileges of the invoking schema, not the owning or defining schema.

demonstrates the fundamental difference between the definer and the invoker rights models. Recall that in , it was necessary for me to push out copies of my application to each regional office so that the code would manipulate the correct tables.

Figure 3.3: Use of invoker rights model to allow a "pass through" to user data

With invoker rights, this step is no longer necessary. Now I can compile the code into a single code repository. When a user from the Northeast region executes the centralized program (probably via a synonym), it will automatically work with tables in the Northeast schema.

So that's the idea behind invoker rights. Let's see what is involved codewise, and then explore how best to exploit the feature.

The syntax to support this feature is simple enough. You add the following clause before your IS or AS keyword in the program header:

AUTHID CURRENT_USER

Here, for example, is a generic "run DDL" engine that relies on the new Oracle 8.1 native dynamic SQL statement EXECUTE IMMEDIATE (described in ) and the invoker rights model:

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

That's certainly lots simpler than the earlier implementation, isn't it?

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. And that's all you have to do. If you do not include the AUTHID clause or if you include it and explicitly request definer rights as shown:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

You have the syntax down and we've covered some rules. Let's try out the new AUTHID clause in a more elaborate example.

A couple of years ago, a group of people started the Stolen Lives Project. The objective of this project is to document the lives "stolen" from families and communities when people are killed by law enforcement officers. I will use this project as the basis for my example in this section.

Neither the Stolen Lives Project nor I believe that all law enforcement officers commit acts of brutality. I also recognize that some killings by officers are justifiable, and that many, if not the vast majority of, officers are committed to improving the lives and guaranteeing the safety of all the citizens in their jurisdiction.

You can get lots more information about the Stolen Lives Project (hereafter referred to as SLP) at the following URL:

http://www.unstoppable.com/22/english/stolenlivesPROJECT

Suppose then that there is a national headquarters for the SLP, and that Headquarters maintains an Oracle database with a separate schema for each city and town in which information is being collected by local law enforcement brutality activists. Each schema has its own stolen_life table, but all schemas perform the same analyses on this information.

The DBA/developer for SLP, Salinda, has upgraded recently to 8.1 and sees an immediate opportunity for the AUTHID feature. She would like to maintain all the code in one place, but when activists in a given city run that code for their locale, they see their data and analyses. shows the architecture Salinda wants to implement.

Figure 3.4: One program analyzes data in different tables

I do not show all the statements needed to set up the various schemas in Oracle; you will find them, however, in the authid.ins file on the companion disk.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Invoker rights programs allow central code to reflect back to the calling schema. Definer rights programs allow remote schemas to access local data (i.e., data in the same schema as the program). Many applications require a combination of these flavors.

Suppose, for example, that the national Stolen Lives Project also maintains a table of "perpetrators," law enforcement officers who have killed one or more people in the United States. Due to the sensitivity of the information, the SLP has decided to maintain a single headquarters table that cannot be accessed directly by the city/town schemas. Yet both the location-specific stolen_life table and the systemwide perpetrators table need to be accessed by the check_city_statistics procedure.

What's a code architect to do? One thought might be to create a public synonym for the perpetrators table and make sure that no city schema has its own perpetrators table. When the city schema runs the central code under invoker rights, the reference to perpetrators would, in fact, be to that central source of data.

That works fine for the check_city_statistics procedure, but what about the rest of the application? With this approach, any city schema can directly access the perpetrators table, a violation of security. So the synonyms solution is no solution at all.

With Oracle 8.1, however, you don't need to do anything more than introduce a layer of code around the shared data structure. You need to do at least that, however, so that you can change the model used for resolving external references.

If the perpetrators table is accessed directly by the check_city_statistics procedure, the reference can only be resolved by the city schema's having direct access (via a synonym) to the table, which is a no-no. The check_city_statistics procedure cannot, therefore, query the perpetrators table directly. Instead, as shown in , it will call another procedure, compiled under the definer rights model, which, in turn, works with the perpetrators table.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

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, for example, that at runtime you can construct a query, a DELETE statement, a CREATE TABLE statement, 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 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 new facility is called native dynamic SQL. I will refer to it as NDS in this chapter.

Here's the free advertisement for Oracle Corporation: NDS is faster and easier than DBMS_SQL. Truth in advertising? Absolutely, although my tests indicate that with the performance enhancements already in place for DBMS_SQL, NDS is on average just slightly faster. There is no doubt, however, that NDS is much easier to use—when you can use it.

Before diving into the syntax and details of NDS, let's take a look at a comparison between the two approaches to dynamic SQL.

Let's compare the DBMS_SQL and NDS implementations of a program that displays all the employees for the specified and very 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;
/

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Let's compare the DBMS_SQL and NDS implementations of a program that displays all the employees for the specified and very 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;
/

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;
/

As you can see (and this is true in general), you can write dramatically less code using NDS. And since the code you write relies less on built-in packaged programs and more on native, standard elements of PL/SQL, that code is easier to build, read, and maintain.

Given this situation, why would anyone use DBMS_SQL ever again? Because NDS cannot do everything and anything you might want to do. The following lists show the operations that can be performed exclusively by each of these dynamic SQL implementations.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

One of the nicest things about NDS is its simplicity. Unlike DBMS_SQL, which has dozens of programs and lots of rules to follow, NDS has been integrated into the PL/SQL language by adding one new statement, EXECUTE IMMEDIATE, and by enhancing the existing OPEN FOR statement:

EXECUTE IMMEDIATE: Executes a specified SQL statement immediately
OPEN FOR: Allows you to perform multiple-row dynamic queries

Use EXECUTE IMMEDIATE to execute (immediately!) the specified SQL statement. Here is the syntax of this statement:

EXECUTE IMMEDIATE SQL_string
   [INTO {define_variable[, define_variable]... | record}]
   [USING [IN | OUT | IN OUT] bind_argument
       [, [IN | OUT | IN OUT] bind_argument]...];

SQL_string: A string expression containing the SQL statement or PL/SQL block
define_variable: A variable that receives a column value returned by a query
record: A record based on a user-defined TYPE or %ROWTYPE that receives an entire row returned by a query
bind_argument: An expression whose value is passed to the SQL statement or PL/SQL block
INTO clause: Use for single-row queries; for each column value returned by the query, you must supply an individual variable or field in a record of compatible type.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Now that you have seen the syntax of OPEN FOR and been introduced to cursor variables, let's explore the nuances involved in multirow queries with NDS.

When you execute an OPEN FOR statement, the PL/SQL runtime engine does the following:

Associates a cursor variable with the query found in the query string
Evaluates any bind arguments and substitutes those values for the placeholders found in the query string
Executes the query
Identifies the result set
Positions the cursor on the first row in the result set
Zeros out the rows-processed count returned by %ROWCOUNT

Note that any bind arguments (provided in the USING clause) in the query are evaluated only when the cursor variable is opened. This means that if you want to use a different set of bind arguments for the same dynamic query, you must issue a new OPEN FOR statement with those arguments.

This approach is actually less efficient than the DBMS_SQL approach, which will allow you to simply rebind and then execute without having to reparse.

To perform a multirow query, you take these steps:

Declare a REF CURSOR type (if one is not already available, as it could be if defined in a package specification).
Declare a cursor variable based on the REF CURSOR.
OPEN the cursor variable FOR your query string.
Use the FETCH statement to fetch one row at a time from the query.
Check cursor attributes (%FOUND, %NOTFOUND, %ROWCOUNT, %ISOPEN) as necessary.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

You have seen several examples of the use of bind variables or arguments with NDS. Let's now go over the various rules and special situations you may encounter when binding.

In most situations, you will be able to take two different paths to insert program values into your SQL string: binding and concatenation. The following table contrasts these approaches for a dynamic UPDATE statement.

Concatenation	Binding
EXECUTE IMMEDIATE 'UPDATE ' \|\| tab 'SET sal = ' \|\| v_sal;	EXECUTE IMMEDIATE 'UPDATE ' \|\| tab 'SET sal = :new_sal' USING v_sal;

Binding involves the use of placeholders and the USING clause; concatenation shortcuts that process by adding the values directly to the SQL string. Two different approaches—which should you use and when?

I recommend that you bind arguments whenever possible (see the next section for limitations on binding) rather than rely on concatenation. There are two reasons for taking this approach:

Binding is faster: When you bind in a value, the SQL string itself does not contain the value, just the placeholder name. Therefore, you can bind different values to the same SQL statement without changing that statement. Since it is the same SQL statement, your application is more likely to be able to take advantage of the pre-parsed cursors that are cached in the System Global Area (SGA) of the database.
Binding is easier to write and maintain

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

One of the most important advantages of NDS over DBMS_SQL is its support for new Oracle8 datatypes: objects and collections. You don't need to change the structure of the code you write in NDS to use it with objects and collections.

Suppose that I am building an internal administrative system for the national health management corporation Health$.Com. To reduce costs, the system will work in a distributed manner, creating and maintaining separate tables of customer information for each for-profit hospital owned by Health$.Com.

I'll start by defining an object type (person) and VARRAY type (preexisting_conditions), as follows:

CREATE TYPE person AS OBJECT (
   name VARCHAR2(50), dob DATE, income NUMBER);
/

CREATE TYPE preexisting_conditions IS TABLE OF VARCHAR2(25);
/

Once these types are defined, I can build a package to manage my most critical health-related information—data needed to maximize profits at Health$.Com. Here is the specification:

/* Filename on companion disk: health$.pkg */
CREATE OR REPLACE PACKAGE health$
AS
   PROCEDURE setup_new_hospital (hosp_name IN VARCHAR2);

   PROCEDURE add_profit_source (
      hosp_name IN VARCHAR2, 
      pers IN Person, 
      cond IN preexisting_conditions);

   PROCEDURE weed_out_poor_and_sick (
      hosp_name VARCHAR2,
      min_income IN NUMBER := 100000,
      max_preexist_cond IN INTEGER := 0);

   PROCEDURE show_profit_centers (hosp_name VARCHAR2);
 END health$;
/

With this package, I can do the following:

Set up a new hospital, which means create a new table to hold information about that hospital. Here's the implementation from the body:

FUNCTION tabname (hosp_name IN VARCHAR2) IS
BEGIN
   RETURN hosp_name || '_profit_center';
END;

PROCEDURE setup_new_hospital (hosp_name IN VARCHAR2) IS
BEGIN
   EXECUTE IMMEDIATE 
      'CREATE TABLE ' || tabname (hosp_name) || ' (
         pers Person, 
         cond preexisting_conditions)
         NESTED TABLE cond STORE AS cond_st';
END;

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

By now, you should have a solid understanding of how native dynamic SQL works in PL/SQL. This section covers some topics that you should be aware of as you start to build production applications with this new PL/SQL feature.

I have created a number of useful generic programs in my presentation on NDS, including functions and procedures that do the following:

Execute any DDL statement
Return the count of rows in any table
Return the count for each grouping by specified column

These are pretty darn useful utilities and I want to let everyone on my development team use them. So I compile them into the COMMON schema and grant EXECUTE authority on the programs to PUBLIC.

However, there is a problem with this strategy. When Sandra connects to her SANDRA schema and executes this command:

SQL> exec COMMON.execDDL ('create table temp (x date)');

she will inadvertently create a table in the COMMON schema—unless I take advantage of the invoker rights model described in

The invoker rights model means that you define your stored programs so they execute under the authority, and with the privileges, of the invoking schema rather than the defining schema (which is the default in Oracle 8.1 and the only option prior to Oracle 8.1).

Fortunately, there isn't much you have to do to take advantage of this new feature. Here is a version of execDDL that executes any DDL statement—but always having an impact in the calling or invoking schema:

CREATE OR REPLACE PROCEDURE execDDL (ddl_string IN VARCHAR2)
   AUTHID CURRENT_USER
IS
BEGIN
   EXECUTE IMMEDIATE ddl_string;
END;
/

I recommend that you use the AUTHID CURRENT_USER clause in all of your dynamic SQL programs, particularly in those that you plan to share among a group of developers. The package discussed at the end of this chapter in follows this standard.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

To make it easier for my readers to take advantage of the various generic utilities discussed in this chapter, I have created a single package called ndsutil. This package, available on the companion disk in ndsutil.pkg, contains the programs listed in .

Table 4.1: Contents of the NDS Utility Package
Name	Description
execImmed	Substitute for EXECUTE IMMEDIATE that does not need a USING or INTO clause; includes error handling.
openFor	Substitute for OPEN FOR that does not need a USING clause; includes error handling.
showCol	Shows the contents of a single column in the specified table.
tabCount	Returns the number of rows in the specified table, with an optional WHERE clause.
countBy	Returns the number of rows in the specified table for a particular GROUP BY expression, with an optional HAVING clause.
dynPLSQL	Executes a dynamic PL/SQL string, automatically making sure that it is a valid block and that it ends in a semicolon. The USING clause is not allowed.

The package is defined using the invoker rights mode (AUTHID CURRENT_USER). This means that no matter who owns the package, any external references in the dynamic SQL you execute via ndsutil are resolved according to the authority of the invoking schema, not the owner.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

We all know that PL/SQL is tightly integrated with the underlying SQL engine in the Oracle database. PL/SQL is the database programming language of choice for Oracle—even if you now can at least theoretically use Java inside the database as well.

But this tight integration does not necessarily mean that there isn't any overhead associated with running SQL from a PL/SQL program. When the PL/SQL runtime engine processes a block of code, it executes the procedural statements within its own engine, but it passes the SQL statements on to the SQL engine. The SQL layer executes the SQL statements and then returns information to the PL/SQL engine, if necessary.

This transfer of control (shown in ) between the PL/SQL and SQL engines is called a context switch. Each time a switch occurs, there is additional overhead. There are a number of scenarios in which many switches occur and performance degrades. Oracle 8.1 now offers two enhancements to PL/SQL that allow you to bulk together multiple context switches into a single switch, thereby improving the performance of your applications.

Figure 5.1: Context switching between PL/SQL and SQL

These new features are as follows:

FORALL: A variation on the FOR loop that bundles together multiple DML statements based on data in a collection
BULK COLLECT: An enhancement to implicit and explicit query cursor syntax that allows the transfer of multiple rows of data in a single round-trip between the PL/SQL and SQL engines

With each new release, PL/SQL gets more robust, faster, and easier to use. The "bulk bind" features discussed in this chapter, FORALL and BULK COLLECT, are clear reflections of this trend.

As you will soon see, you won't be able to take advantage of FORALL and BULK COLLECT unless you work with collections (variable arrays, nested tables, or index-by tables). Sadly, many PL/SQL developers have not yet become conversant in these relatively new structures (variable arrays and nested tables are new with Oracle8; index-by tables were first available in PL/SQL 2.3 as "PL/SQL tables"). If you are one of those developers, perhaps these bulk bind capabilities will offer the required incentive for you to dig in to your books and knowledge bases.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Before we take a look at the details of FORALL and BULK COLLECT, let's examine the scenarios where excessive context switches are likely to cause problems. These are likely to happen when you are processing multiple rows of information stored (or to be deposited) in a collection (a VARRAY, nested table, index-by table, or host array).

Suppose, for example, that I have filled two variable arrays with war criminals' ID numbers and the latest count of their victims. I then want to update the war criminals table with this information. Here's the solution I would have written prior to Oracle 8.1 (referencing a couple of already defined variable arrays):

CREATE OR REPLACE PROCEDURE update_tragedies (
   warcrim_ids IN name_varray,
   num_victims IN number_varray
   )
IS
BEGIN
   FOR indx IN warcrim_ids.FIRST .. warcrim_ids.LAST 
   LOOP
      UPDATE war_criminal
         SET victim_count = num_victims (indx)
       WHERE war_criminal_id = warcrim_ids (indx);
   END LOOP;
END;

If I needed to update 100 rows, then I would be performing 100 context switches, since each update is processed in a separate trip to the SQL engine. illustrates this excessive (but previously unavoidable) switching.

Figure 5.2: Excessive context switching for multiple UPDATEs

You can also run into lots of switching when you fetch multiple rows of information from a cursor into a collection. Here is an example of the kind of code that cries out for the Oracle 8.1 bulk collection feature:

DECLARE
   CURSOR major_polluters IS
      SELECT name, mileage
        FROM cars_and_trucks
       WHERE vehicle_type IN ('SUV', 'PICKUP');
   names name_varray := name_varray();
   mileages number_varray := number_varray();
BEGIN
   FOR bad_car IN major_polluters
   LOOP
      names.EXTEND;
      names (major_polluters%ROWCOUNT) := bad_car.name;
      mileages.EXTEND;
      mileages (major_polluters%ROWCOUNT) := bad_car.mileage;
   END LOOP;

   ... now work with data in the arrays ...
END;

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

PL/SQL has a new keyword: FORALL. This keyword tells the PL/SQL runtime engine to bulk bind into the SQL statement all the elements of one or more collections before sending anything to the SQL engine.

Table of Contents