pls-00436 removed in oracle 11g

Since its release in Oracle 8i, there has been a frustrating restriction with FORALL: the PLS-00436 "implementation restriction". Readers who are familiar with FORALL will be aware of PLS-00436. It is the implementation restriction that prevents us from accessing specific record attributes within a FORALL DML construct. One of the first things I check whenever there is a new major release of Oracle is whether this restriction has been removed. With the release of 11g, it has (finally) and this short article will demonstrate the techniques that are now available to us.

Readers who are less familiar with this restriction (or wish to recap), should read this oracle-developer.net article for an overview, including suggested workarounds.

a simple example

Using the examples from the article referenced above, we will perform a simple update on a column in EMP using FORALL. This is for demonstration purposes only: under normal circumstances the code would be sufficiently complex to warrant the use of PL/SQL over SQL in the first place. The example is as follows.

SQL> DECLARE
  2
  3     TYPE emp_aat IS TABLE OF emp%ROWTYPE
  4        INDEX BY PLS_INTEGER;
  5     aa_emps emp_aat;
  6
  7  BEGIN
  8
  9     /* Fetch the data... */
 10     SELECT * BULK COLLECT INTO aa_emps
 11     FROM   emp;
 12
 13     /* Update the SAL column... */
 14     FORALL i IN 1 .. aa_emps.COUNT
 15        UPDATE emp
 16        SET    sal = aa_emps(i).sal * 1.1
 17        WHERE  empno = aa_emps(i).empno;
 18
 19     DBMS_OUTPUT.PUT_LINE(
 20        TO_CHAR(SQL%ROWCOUNT) || ' rows updated.'
 21        );
 22
 23  END;
 24  /

14 rows updated.

PL/SQL procedure successfully completed.

In versions prior to 11g, both lines 16 and 17 would raise PLS-00436 because they reference attributes within the EMP record variable (or to be precise, attributes of each EMP record element within the associative array). As we can see from the above, however, this restriction has gone.

implications for inserts

The removal of the PLS-00436 restriction simplifies several areas of PL/SQL FORALL coding, including inserts. As discussed in the referenced background article, FORALL INSERTs that involve a partial set of target columns can be achieved in previous versions using a record-based insert into an in-line view over the target table (an 8i alternative would be to have an array for each target column). The relaxation of PLS-00436 removes the need for such coding techniques, however. We can now use a simple VALUES () list, as we will see below.

In the following example, we will create a copy of the EMP table and populate it with a subset of columns. First, we create the table as follows.

SQL> CREATE TABLE emp_copy
  2  AS
  3     SELECT *
  4     FROM   emp
  5     WHERE  ROWNUM < 1;

Table created.

We can now populate a subset of the target columns using a "traditional" INSERT..VALUES statement in our FORALL construct, without the need for any workarounds, as follows.

SQL> DECLARE
  2
  3     CURSOR c_emps IS
  4        SELECT empno
  5        ,      ename
  6        ,      job
  7        ,      hiredate
  8        FROM   emp;
  9
 10     TYPE emp_aat IS TABLE OF c_emps%ROWTYPE
 11        INDEX BY PLS_INTEGER;
 12     aa_emps emp_aat;
 13
 14  BEGIN
 15
 16     /* Bulk fetch sample data... */
 17     OPEN c_emps;
 18     FETCH c_emps BULK COLLECT INTO aa_emps;
 19     CLOSE c_emps;
 20
 21     /* FORALL INSERT..VALUES for the first time with record-arrays... */
 22     FORALL i IN INDICES OF aa_emps
 23        INSERT INTO emp_copy ( empno
 24                             , ename
 25                             , job
 26                             , hiredate
 27                             )
 28        VALUES ( aa_emps(i).empno
 29               , aa_emps(i).ename
 30               , aa_emps(i).job
 31               , aa_emps(i).hiredate
 32               );
 33
 34     DBMS_OUTPUT.PUT_LINE(
 35        TO_CHAR(SQL%ROWCOUNT) || ' rows inserted.'
 36        );
 37
 38  END;
 39  /

14 rows inserted.

PL/SQL procedure successfully completed.

implications for row-based updates

Oracle 9i introduced record-based inserts and updates for PL/SQL (described here). Record-based inserts are convenient when dealing with entire target records or as a workaround to PLS-00436 (prior to 11g of course). Record-based updates, defined by the SET ROW syntax, have proved to be less useful with FORALL because of PLS-00436. One of the reasons is that we cannot constrain the updates without additional arrays to store the keys for each target update (details and examples of this are available in the article referenced above).

The removal of the PLS-00436 restriction means that the SET ROW syntax is more accessible than in previous versions. We still have the issue of updates to all columns, including the primary key (although a workaround to this is described here). The following short example demonstrates the technique, though for simplicity we haven't included the workaround that prevents the primary key from being updated.

SQL> DECLARE
  2
  3     TYPE emp_aat IS TABLE OF emp%ROWTYPE
  4        INDEX BY PLS_INTEGER;
  5     aa_emps emp_aat;
  6
  7  BEGIN
  8
  9     /* Bulk fetch sample data... */
 10     SELECT * BULK COLLECT INTO aa_emps
 11     FROM   emp;
 12
 13     /* Can now constrain update from main array... */
 14     FORALL i IN INDICES OF aa_emps
 15        UPDATE emp
 16        SET    ROW   = aa_emps(i)
 17        WHERE  empno = aa_emps(i).empno;
 18
 19     DBMS_OUTPUT.PUT_LINE(
 20        TO_CHAR(SQL%ROWCOUNT) || ' rows updated.'
 21        );
 22
 23  END;
 24  /

14 rows updated.

PL/SQL procedure successfully completed.

In versions prior to 11g, the inclusion of the predicate in line 17 needed to be served by a separate array. 11g makes this much simpler.

a final consideration

One of the workarounds to the PLS-00436 restriction in previous versions was to use SQL object types instead of PL/SQL record types and use the TREAT function to access the individual attributes. The workaround article (referenced earlier) notes that the object type method is comparable in performance to the alternatives available at that time. Now that the PLS-00436 restriction has been removed, we will compare the workaround to the new, simpler syntax. To enable us to do this, we will create an EMP_LARGE table with 100,000 rows, as follows.

SQL> CREATE TABLE emp_large
  2  NOLOGGING
  3  AS
  4     SELECT *
  5     FROM   emp
  6     WHERE  ROWNUM < 1;

Table created.

SQL> ALTER TABLE emp_large MODIFY empno NUMBER;

Table altered.

SQL> ALTER TABLE emp_large ADD PRIMARY KEY (empno);

Table altered.

SQL> INSERT /*+ APPEND */ INTO emp_large
  2  SELECT ROWNUM
  3  ,      ename
  4  ,      job
  5  ,      mgr
  6  ,      hiredate
  7  ,      sal
  8  ,      comm
  9  ,      deptno
 10  FROM   emp
 11  ,     (SELECT * FROM all_objects)
 12  WHERE  ROWNUM <= 100000;

100000 rows created.

SQL> COMMIT;

Commit complete.

For the workaround, we will require an object type "record" of EMP_LARGE and a collection of the object, as follows.

SQL> CREATE TYPE emp_large_ot AS OBJECT
  2  ( empno    NUMBER
  3  , ename    VARCHAR2(10)
  4  , job      VARCHAR2(9)
  5  , mgr      NUMBER(4)
  6  , hiredate DATE
  7  , sal      NUMBER(7,2)
  8  , comm     NUMBER(7,2)
  9  , deptno   NUMBER(2)
 10  );
 11  /

Type created.

SQL> CREATE TYPE emp_large_ntt AS TABLE OF emp_large_ot;
  2  /

Type created.

We will now compare the time it takes to simply update each column in EMP_LARGE with itself, using both the workaround syntax and the newly-supported syntax. We will use a version of Tom Kyte's RUNSTATS utility to compare the time and resources used by each approach. We will begin with the workaround method, as follows.

SQL> exec runstats_pkg.rs_start;

PL/SQL procedure successfully completed.

SQL> DECLARE
  2
  3     CURSOR c_emps IS
  4        SELECT emp_large_ot(empno, ename, job, mgr,
  5                            hiredate, sal, comm, deptno)
  6        FROM   emp_large;
  7
  8     nt_emps emp_large_ntt;
  9     v_cnt   PLS_INTEGER := 0;
 10
 11  BEGIN
 12
 13     OPEN c_emps;
 14     LOOP
 15
 16        FETCH c_emps BULK COLLECT INTO nt_emps LIMIT 500;
 17
 18        FORALL i IN INDICES OF nt_emps
 19           UPDATE emp_large
 20           SET    ename    = TREAT(nt_emps(i) AS emp_large_ot).ename
 21           ,      job      = TREAT(nt_emps(i) AS emp_large_ot).job
 22           ,      mgr      = TREAT(nt_emps(i) AS emp_large_ot).mgr
 23           ,      hiredate = TREAT(nt_emps(i) AS emp_large_ot).hiredate
 24           ,      sal      = TREAT(nt_emps(i) AS emp_large_ot).sal
 25           ,      comm     = TREAT(nt_emps(i) AS emp_large_ot).comm
 26           ,      deptno   = TREAT(nt_emps(i) AS emp_large_ot).deptno
 27           WHERE  empno    = TREAT(nt_emps(i) AS emp_large_ot).empno;
 28
 29        v_cnt := v_cnt + SQL%ROWCOUNT;
 30
 31        EXIT WHEN c_emps%NOTFOUND;
 32
 33     END LOOP;
 34     CLOSE c_emps;
 35
 36     DBMS_OUTPUT.PUT_LINE(TO_CHAR(v_cnt) || ' records updated.');
 37
 38     COMMIT;
 39
 40  END;
 41  /

100000 records updated.

PL/SQL procedure successfully completed.

We will now run the newly-supported (and much simpler) version, as follows.

SQL> exec runstats_pkg.rs_middle;

PL/SQL procedure successfully completed.

SQL> DECLARE
  2
  3     CURSOR c_emps IS
  4        SELECT *
  5        FROM   emp_large;
  6
  7     TYPE emp_large_ntt IS TABLE OF c_emps%ROWTYPE;
  8
  9     nt_emps emp_large_ntt;
 10     v_cnt   PLS_INTEGER := 0;
 11
 12  BEGIN
 13
 14     OPEN c_emps;
 15     LOOP
 16
 17        FETCH c_emps BULK COLLECT INTO nt_emps LIMIT 500;
 18
 19        FORALL i IN INDICES OF nt_emps
 20           UPDATE emp_large
 21           SET    ename    = nt_emps(i).ename
 22           ,      job      = nt_emps(i).job
 23           ,      mgr      = nt_emps(i).mgr
 24           ,      hiredate = nt_emps(i).hiredate
 25           ,      sal      = nt_emps(i).sal
 26           ,      comm     = nt_emps(i).comm
 27           ,      deptno   = nt_emps(i).deptno
 28           WHERE  empno    = nt_emps(i).empno;
 29
 30        v_cnt := v_cnt + SQL%ROWCOUNT;
 31
 32        EXIT WHEN c_emps%NOTFOUND;
 33
 34     END LOOP;
 35     CLOSE c_emps;
 36
 37     DBMS_OUTPUT.PUT_LINE(TO_CHAR(v_cnt) || ' records updated.');
 38
 39     COMMIT;
 40
 41  END;
 42  /

100000 records updated.

PL/SQL procedure successfully completed.

Finally, we report the differences as follows.

SQL> exec runstats_pkg.rs_stop(500);

Run1 ran in 954 hsecs
Run2 ran in 534 hsecs
Run1 ran in 178.65% of the time


Name                                  Run1        Run2        Diff
LATCH.checkpoint queue latch         5,129       4,599        -530
LATCH.cache buffers chains         671,941     670,361      -1,580
STAT..redo size                 30,436,556  30,432,204      -4,352
STAT..physical read bytes          901,120     778,240    -122,880
STAT..physical read total byte   1,933,312   1,466,368    -466,944
STAT..session pga memory         4,390,912     589,824  -3,801,088
STAT..physical write total byt  16,637,952  11,091,968  -5,545,984


Run1 latches total versus run2 -- difference and pct
Run1        Run2        Diff        Pct
730,801     726,947      -3,854    100.53%

PL/SQL procedure successfully completed.

When compared with the newly-supported syntax, the inherent cost of using object types in the workaround becomes apparent (note that there is no direct evidence above to support this statement: just several years of experience using object types in various performance scenarios). In the above example, the new method ran in just over half the time of the workaround method. The object method had slightly more physical I/O to do, but in repeated runs of the comparison, the same results appear, with the workaround taking over twice as long in some comparisons.

source code

The source code for the examples in this article can be downloaded from here.

Adrian Billington, November 2007

oracle-developer.net