Example 1. Thin wrapper for executing SQL statements.

BEGIN
   FOR department IN  (SELECT department_id d, department_name
                         FROM departments
                     ORDER BY department_name)
   LOOP
      DBMS_OUTPUT.PUT_LINE (   CHR (10)
                            || department.department_name);

      FOR employee IN  (SELECT last_name
                          FROM employees
                         WHERE department_id = department.d
                      ORDER BY last_name)
      LOOP
         DBMS_OUTPUT.PUT_LINE (   '- '
                               || employee.last_name);
      END LOOP;
   END LOOP;
END;

There are many other applications and programs, however, that rely on PL/SQL to perform computationally-intensive tasks that are all but completely independent of the database. It is, after all, a fully functional procedural language. Consider, for example, the code shown in Example 2. This program takes on the task of finding all right-angled triangles with all side lengths integer (a.k.a. perfect triangles). We must count only unique triangles-that is, those whose sides are not each the same integral multiple of the sides of a perfect triangle already found.

Example 2. Computing perfect triangles.

CREATE OR REPLACE PROCEDURE perfect_triangles (p_max IN INTEGER)
IS
   t1             INTEGER;
   t2             INTEGER;
   long           INTEGER;
   short          INTEGER;
   hyp            NUMBER;
   ihyp           INTEGER;

   TYPE side_r IS RECORD (
      short                         INTEGER,
      long                          INTEGER);

   TYPE sides_t IS TABLE OF side_r
      INDEX BY BINARY_INTEGER;

   unique_sides   sides_t;
   n      INTEGER := 0 /* curr max elements in unique_sides */;
   dup_sides      sides_t;
   m      INTEGER := 0 /* curr max elements in dup_sides */;

   PROCEDURE store_dup_sides (p_long IN INTEGER, p_short IN INTEGER)
   IS
      mult         INTEGER := 2;
      long_mult    INTEGER :=  p_long * 2;
      short_mult   INTEGER :=  p_short * 2;
   BEGIN
      WHILE (long_mult < p_max)
         OR (short_mult < p_max)
      LOOP
         n := n + 1;
         dup_sides (n).long := long_mult;
         dup_sides (n).short := short_mult;
         mult := mult + 1;
         long_mult := p_long * mult;
         short_mult := p_short * mult;
      END LOOP;
   END store_dup_sides;

   FUNCTION sides_are_unique (p_long IN INTEGER, p_short IN INTEGER)
      RETURN BOOLEAN
   IS
   BEGIN
      FOR j IN 1 .. n
      LOOP
         IF      (p_long = dup_sides (j).long)
             AND (p_short = dup_sides (j).short)
         THEN
            RETURN FALSE;
         END IF;
      END LOOP;

      RETURN TRUE;
   END sides_are_unique;
BEGIN                            /* Perfect_Triangles */
   t1 := DBMS_UTILITY.get_time;

   FOR long IN 1 .. p_max
   LOOP
      FOR short IN 1 .. long
      LOOP
         hyp := SQRT (  long * long + short * short);
         ihyp := FLOOR (hyp);

         IF   hyp
            - ihyp < 0.01
         THEN
            IF (ihyp * ihyp =
                        long * long
                      + short * short
               )
            THEN
               IF sides_are_unique (long, short)
               THEN
                  m := m + 1;
                  unique_sides (m).long := long;
                  unique_sides (m).short := short;
                  store_dup_sides (long, short);
               END IF;
            END IF;
         END IF;
      END LOOP;
   END LOOP;

   t2 := DBMS_UTILITY.get_time;
   DBMS_OUTPUT.put_line (
         CHR (10)
      || TO_CHAR (((  t2
                    - t1
                   ) / 100
                  ), '9999.9')
      || ' sec'
   );
END perfect_triangles;

This program implements an exhaustive search among candidate triangles with all possible combinations of lengths of the two shorter sides, each in the range of one to a specified maximum. Testing whether the square root of the sum of the squares of the two short sides is within 0.01 of an integer coarsely filters each candidate. Exactly applying Pythagoras's theorem using integer arithmetic tests triangles that pass this test. Candidate- perfect triangles are tested against the list of multiples of perfect triangles found so far. Each new unique perfect triangle is stored in a PL/SQL table, and its multiples (up to the maximum length) are stored in a separate PL/SQL table to facilitate uniqueness testing.

The implementation thus involves a doubly nested loop with these steps at its heart: several arithmetic operations, casts and comparisons; calls to procedures implementing comparisons driven by iteration through a PL/SQL table (with yet more arithmetic operations); and extension of PL/SQL tables where appropriate.

So what impact does native compilation have on such code? The elapsed time was measured for p_max =5000 (that is, 12.5 million repetitions of the heart of the loop) using interpreted and natively compiled versions of the procedure. The times were 548 seconds and 366 seconds, respectively (on a Sun Ultra60 with no load apart from the test). Thus, the natively compiled version was about 33 percent faster.

That's not bad for a semi-transparent enhancement. In other words, no code changes were required in our application. And, while for data-intensive programs, native compilation may give only a marginal performance improvement, we've never seen it give performance degradation. So how do you turn on native compilation? Read on...

One-Time DBA Setup

Native PL/SQL compilation is achieved by translating the PL/SQL source code into C source code that's then compiled on the given platform. The compiling and linking of the generated C source code is done using third-party utilities whose location has been specified by the DBA, typically in the init.ora parameter file. (Check the Release Notes for your platform to see which third-party utilities are supported.)

The object code for each natively compiled PL/SQL library unit is stored on the platform's file system in directories, similarly under the DBA's control. Thus, native compilation does take longer than interpreted-mode compilation. Our tests have shown a factor of about times two. This is because it involves these extra steps: generating C code from the initial output of the PL/SQL compilation; writing this to the file system; invoking and running the C compiler; and linking the resulting object code into Oracle.

Oracle recommends that the C compiler be configured to do no optimization. Our tests have shown that optimizing the generated C produces negligible improvement in runtime performance but substantially increases the compilation time.

The DBA should appreciate that the utilities for compilation and linking must be under his or her strict control, owned by the Oracle user or root (or its equivalent on non-Unix systems) and with write-access granted only to these users. You already protect the Oracle executables in this way, for obvious reasons. You can imagine what might happen if you didn't! Well, the risk would be just the same if a malicious user could subvert the utilities for compilation and linking used by native compilation.

Choosing Between Interpreted and Native Compilation Modes

The compiler mode is determined by the session parameter plsql_compiler_flags. The user may set it as follows:

ALTER SESSION 
   SET plsql_compiler_flags = 
   'NATIVE' /* or 'INTERPRETED' */;

The compilation mode is then set for subsequently compiled PL/SQL library units (during that session). The mode is stored with the library unit's metadata, so that if the program is implicitly recompiled as a consequence of dependency checking, the original mode the user intended will be used.

You can determine the compilation mode by querying the data dictionary using the SELECT statement shown in Example 3.

Example 3. Determining the compilation mode of a program unit.

SELECT o.object_name NAME, s.param_value comp_mode

  FROM USER_STORED_SETTINGS s, 
       USER_OBJECTS o

 WHERE o.object_id = s.object_id
   AND param_name = 'plsql_compiler_flags'
   AND o.object_type IN ('PACKAGE', 'PROCEDURE', 'FUNCTION');

One thing to be aware of: If you use DBMS_UTILITY.COMPILE_SCHEMA to attempt to recompile all invalid program units in your schema, it will use the current value of plsql_compiler_flags rather than the compilation mode stored with each program unit. (In other words, it does the equivalent of alter ... compile without reuse settings for the whole schema.)

Oracle recommends that all the PL/SQL library units that are called from a given top- level unit be compiled in the same mode. This is because there's a cost for the context switch when a library unit compiled in one mode invokes one compiled in the other mode, particularly when a native unit calls an interpreted unit. Significantly, this recommendation includes the Oracle-supplied library units. These are always shipped (in the seed databases) compiled in interpreted mode.

Bulk Binding in Native Dynamic SQL

One of the most exciting new features in Oracle8i was the native dynamic SQL: an implementation of dynamic SQL that relies on two native statements (EXECUTE IMMEDIATE and OPEN FOR) to run dynamically constructed SQL statements at runtime. NDS is dramatically easier to use than DBMS_SQL and, in most cases, is noticeably faster. But NDS wasn't the only new feature for improving SQL performance and flexibility. Oracle also introduced "bulk binding" for DML and queries.

And never the twain could meet. In other words, we couldn't use bulk binding to run a dynamic SQL statement. In Oracle 9i, all that's changed. We can now take advantage of these two great features together. This section refreshes your knowledge of bulk binding and then shows how to use it with dynamic SQL, finishing up with an explanation of improved error handling for bulk-bind operations.

Suppose we need to write a program to populate elements of a PL/SQL collection from a SELECT query. Our first pre-Oracle8i inclination would be to write what you see in Example 5.

Example 5. Using a cursor FOR loop to populate a collection.

DECLARE
   TYPE employee_ids_t IS 
      TABLE OF employees.employee_id%TYPE
      INDEX BY BINARY_INTEGER;

   employee_ids   employee_ids_t;
   n              INTEGER        := 0;
BEGIN
   FOR j IN  (SELECT employee_id
                FROM employees
               WHERE salary < 3000)
   LOOP
      n :=  n + 1;
      employee_ids (n) := j.employee_id;
   END LOOP;
END;

Using this approach, each explicit row-by-row assignment of the collection element to the cursor component causes a context switch between the PL/SQL engine and the SQL engine resulting in performance overhead. With Oracle8i and above, we can move away from these context switches (and all that code) by performing a BULK COLLECTION, as shown here:

BEGIN
   SELECT employee_id
     BULK COLLECT INTO employee_ids
     FROM employees
    WHERE salary < 3000;
   ...
END;

Wow! That's quite a change in code volume. And you'll see substantial improvements in performance, as well, by minimizing the number of context switches required to execute the block.

But what if you want to execute this same query (and then process the rows) for one of any number of different employee tables, segregated by location?

In Oracle 9i, we can take the preceding query and transform it into a dynamic SQL query that will handle this additional complexity. There are many application implementation situations that require dynamic SQL. Native dynamic SQL (execute immediate and related constructs) is usually preferred over Dbms_Sql because it's easier to write and proofread and executes faster. However, pre-Oracle9i, only Dbms_Sql could be used for dynamic bulk binding. Oracle 9i introduces the following syntax for bulk binding in native dynamic SQL:

CREATE OR REPLACE PROCEDURE
   process_employees (loc_in IN VARCHAR2)
IS
BEGIN
   EXECUTE IMMEDIATE 
     'SELECT employee_id FROM '
     || loc_in || '_employees'
     BULK COLLECT INTO employee_ids;
   ...
END;

It's so nice to see the fairly arbitrary restrictions in the PL/SQL language falling away with each new release! Let's take a look at how you implement "in-binding" (binding variables into the dynamic SQL), "out-binding" (extracting values from the dynamic SQL), and error handling when using bulk operations on dynamic SQL statements.

In-Binding

Both the EXECUTE IMMEDIATE and FORALL (for bulk DML operations) offer a USING clause to bind variable values into the SQL statement. Let's follow the progression of explicit row-from-row processing to bulk binding to bulk binding in native dynamic DML to see how the USING clause is deployed.

We start with this kind of explicit FOR loop in our Oracle7 and Oracle8 code base:

FOR indx IN 
   employee_ids.FIRST .. employee_ids.LAST
LOOP
   UPDATE employees
      SET salary = salary * 1.1
    WHERE employee_id = employee_ids (indx);
END LOOP;

Then, with Oracle8i, we get rid of most of the context switches by moving to FORALL:

FORALL indx IN 
   employee_ids.FIRST .. employee_ids.LAST
  UPDATE employees
     SET salary = salary * 1.1
   WHERE employee_id = employee_ids (indx);

And that handles all of our needs-unless, once again, we need or would like to perform this same operation on different tables, based on location (or for any other kind of dynamic SQL situation). In this case, we can combine FORALL with EXECUTE IMMEDIATE, with these wonderful results:

CREATE OR REPLACE PROCEDURE upd_employees (
  loc_in IN VARCHAR2,
  employees_in IN employees_t )
IS
BEGIN
   FORALL indx in
      employees_in.first..
    employees_in.last
    EXECUTE IMMEDIATE
     'UPDATE '
        || loc_in
          || ' employees
        SET salary = salary*1.1'
          || ' WHERE employee_id = :the_id'
      USING employee_in (indx);
END;

Notice that in the USING clause, we must include both the name of the collection and the subscript for a single row using the same FORALL loop index variable.

Out-Binding

Let's again follow the progression from individual row updates to bulk bind relying on BULK COLLECT INTO to retrieve information, and finally the dynamic approach possible in Oracle 9i.

Oracle8 enhanced DML capabilities by providing support for the RETURNING clause. Shown in the following FOR loop, it allows us to obtain information (in this case, the updated salary) from the DML statement itself (thereby avoiding a separate and expensive query).

BEGIN
   FOR indx IN 
      employee_ids.FIRST .. employee_ids.LAST
   LOOP
      UPDATE    employees
            SET salary = salary * 1.1
          WHERE employee_id = employee_ids (indx)
      RETURNING salary
           INTO salaries (indx);
   END LOOP;
END;

Starting with Oracle8i, we can take advantage of FORALL to improve performance dramatically:

BEGIN
   FORALL indx IN employee_ids.FIRST .. employee_ids.LAST
      UPDATE    employees
            SET salary = salary * 1.1
          WHERE employee_id = employee_ids (indx)
      RETURNING salary
           BULK COLLECT INTO salaries;
END;

There's one seemingly odd aspect of this code you should remember: Inside the DML statement, any reference to the collection that drives the FORALL statement must be subscripted as in:

WHERE employee_id = employee_ids (indx)

In the RETURNING clause, however, you BULK COLLECT INTO the collection and not a single subscripted row of the collection.

That's all well and good, but what if (not to sound like a broken record) we want to execute this same update for any of the employee tables for different locations? Time to go to NDS and, with Oracle 9i only, also employ a RETURNING BULK COLLECT clause:

CREATE OR REPLACE PROCEDURE
   upd_employees (
      loc_in IN VARCHAR2,
    employees_in IN employees_t
)
IS
   my_salaries salaries_t;
BEGIN
   FORALL indx in
      employees_in.first..
    employees_in.last
    EXECUTE IMMEDIATE
     'UPDATE '
        || loc_in
          || ' employees
        SET salary = salary*1.1'
          || ' WHERE employee_id = :the_id
         RETURNING salary INTO :salaries'
      USING employee_in (indx)
    RETURNING BULK COLLECT INTO
       my_salaries;
END;

Handling and Reporting Exceptions

In Oracle8i, FORALL was wonderful for rapid processing of bulk DML statements. One problem with it, however, is that you lose some of the granularity in exception handling that you help with row-by-row processing. Suppose, for example, that we want to load a whole bunch of words into a vocabulary table. We can do it very efficiently as follows:

BEGIN
   FORALL indx IN 
      words.FIRST .. words.LAST
    INSERT INTO vocabulary
                  (text)
        VALUES (words (indx));
END;

If, however, an error occurred in any single INSERT, the entire FORALL statement would fail. This is the sort of scenario that was easily handled with row-level processing, such as with a loop like this:

FOR indx IN words.FIRST .. words.LAST
LOOP
   BEGIN
      INSERT INTO t text)
           VALUES (words (indx));
   EXCEPTION
      WHEN OTHERS
      THEN
         error_codes (indx) := SQLERRM;
   END;
END LOOP;

With this kind of code, we'd insert all rows that didn't cause an error to occur. With Oracle 9i, you can now do this with both static and dynamic FORALL SQL statements, by taking advantage of the SAVE EXCEPTIONS clause.

FORALL indx IN
    words.first..words.last
  SAVE EXCEPTIONS 
  INSERT INTO vocabulary ( text ) 
     VALUES ( words(indx) );

Use of SAVE EXCEPTIONS allows the FORALL to continue through all the rows indicated by the collection; it "saves up" the exceptions as it encounters them. This saving step begs the obvious question: How can you, the developer, get information about the errors that were "saved"? By taking advantage of the new SQL%BULK_COLLECTIONS pseudo-collection, as demonstrated in the code shown in Example 6.

Example 6. Using the SQL%BULK_COLLECTIONS pseudo-collection.

DECLARE
  bulk_errors   EXCEPTION;
  PRAGMA EXCEPTION_INIT (bulk_errors,  -24381);
BEGIN
  FORALL indx IN words.FIRST .. words.LAST
    SAVE EXCEPTIONS
    INSERT INTO t (text)
        VALUES (words (indx));
EXCEPTION
  WHEN bulk_errors
  THEN
    FOR j IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
    LOOP
      log_error (
         SQL%BULK_EXCEPTIONS(indx).ERROR_INDEX,
         SQLERRM(-1 * SQL%BULK_EXCEPTIONS(indx).ERROR_CODE)
      );
    END LOOP;
END;

Each row of this pseudo-collection is a record consisting of two fields: ERROR_INDEX and ERROR_CODE. The former field shows which index in the original bulk-load collection causes the failure. ERROR_CODE is the error number encountered.

You must both use the SAVE EXCEPTIONS construct and handle the BULK_ERRORS exception to get the intended benefit (that is, that all non-erroring rows are inserted).

The March of Progress

The new Oracle 9i features covered in this article should provide a comfortable feeling about Oracle's commitment to the PL/SQL language. Native compilation offers a path to formidable and transparent improvements in our application performance (even, and perhaps especially, existing "legacy" code). The CASE statement and expression, as well as support for dynamic SQL in bulk binding, help round out the language semantics.