avoiding pls-00436 with forall

Developers who are regular users of FORALL and record-based collections/arrays will be familiar with PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND table of records. This restriction prevents us from referencing individual attributes of records with FORALL. This article demonstrates workarounds to this issue for versions 9i and 10g. Update: note that the restriction has finally been removed in 11g, as described in this oracle-developer.net article.

pls-00436

To begin, we'll take a look at the implementation restriction itself. In the following example, we'll try to update EMP from an associative array of records. Within the FORALL statement, we'll attempt to access specific attributes of the array record, using standard array(index).attribute syntax.

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     FORALL i IN 1 .. aa_emps.COUNT
 10        UPDATE emp
 11        SET    sal = aa_emps(i).sal * 1.1
 12        WHERE  empno = aa_emps(i).empno;
 13
 14  END;
 15  /

      SET    sal = aa_emps(i).sal * 1.1
                   *
ERROR at line 11:
ORA-06550: line 11, column 20:
PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND table of records
ORA-06550: line 11, column 20:
PLS-00382: expression is of wrong type
ORA-06550: line 12, column 22:
PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND table of records
ORA-06550: line 12, column 22:
PLS-00382: expression is of wrong type
ORA-06550: line 11, column 20:
PL/SQL: ORA-22806: not an object or REF
ORA-06550: line 10, column 7:
PL/SQL: SQL Statement ignored

The message is quite clear. We simply cannot access the attributes of the array record.

workaround to inserts

In our previous example, we used an associative array of records to update the table with. There is no viable workaround to the PLS-00436 restriction when using associative arrays for updates/deletes (we need collections: see next section). If we need to insert data, however, then we can use record-based inserts introduced in 9i.

It is quite common (particularly in batch systems) to have wide tables. These often cater for different attributes available from different source feeds, so we frequently need to reference different columns in our inserts. The PLS-00436 restriction makes it impossible to subset the columns directly, as we've seen, so we need to use the following workaround that removes the need to reference individual attributes.

A workaround we can use for this combines a record-based insert with an in-line view. In the example that follows, we'll imagine that the EMP table is our "wide" table and that we need to load a subset of columns.

SQL> DECLARE
  2
  3     TYPE subset_rt IS RECORD
  4     ( empno    emp.empno%TYPE
  5     , ename    emp.ename%TYPE
  6     , hiredate emp.hiredate%TYPE
  7     , deptno   emp.deptno%TYPE );
  8
  9     TYPE subset_aat IS TABLE OF subset_rt
 10        INDEX BY PLS_INTEGER;
 11
 12     aa_subset subset_aat;
 13
 14  BEGIN
 15
 16     /* Some "source" data... */
 17     SELECT ROWNUM, owner, created, 20
 18     BULK   COLLECT INTO aa_subset
 19     FROM   all_objects
 20     WHERE  ROWNUM <= 10;
 21
 22     /* Record-based insert and subset of columns... */
 23     FORALL i IN 1 .. aa_subset.COUNT
 24        INSERT INTO (SELECT empno, ename, hiredate, deptno FROM emp)
 25        VALUES aa_subset(i);
 26
 27     DBMS_OUTPUT.PUT_LINE(TO_CHAR(SQL%ROWCOUNT) || ' rows inserted.');
 28
 29  END;
 30  /

10 rows inserted.

PL/SQL procedure successfully completed.

We can see that this is quite a simple solution. If we need to update or delete, however, then we need a far more flexible solution. Fortunately, there is a method available, as described in the following section.

workaround using objects and collections

The previous solution enables us to subset a list of columns in a FORALL statement, but still doesn't provide direct access to the record attributes themselves. For a more general purpose workaround, therefore, we can turn to objects and collections.

This workaround uses objects, collections and the 9i TREAT function. The TREAT function casts an object instance back to its underlying data type. By doing so, it gives us direct access to attributes within a FORALL statement, as we shall see. First, we'll create an object type to represent a "record" of EMP data and then create a corresponding collection type.

SQL> CREATE TYPE emp_ot AS OBJECT
  2  ( empno NUMBER(4)
  3  , ename VARCHAR2(10)
  4  , sal   NUMBER(7,2)
  5  );
  6  /

Type created.

SQL> CREATE TYPE emp_ntt AS TABLE OF emp_ot;
  2  /

Type created.

Now we'll fetch a collection of EMP records and then perform a FORALL UPDATE, referencing some of the attributes directly in each record of the collection.

SQL> DECLARE
  2  
  3     nt_emp emp_ntt;
  4  
  5  BEGIN
  6  
  7     /* Populate the demo collection... */
  8     SELECT emp_ot(empno, ename, sal) BULK COLLECT INTO nt_emp
  9     FROM   emp;
 10  
 11     /* FORALL with workaround... */
 12     FORALL i IN 1 .. nt_emp.COUNT
 13        UPDATE emp
 14        SET    ename = TREAT(nt_emp(i) AS emp_ot).ename
 15        ,      sal   = TREAT(nt_emp(i) AS emp_ot).sal * 1.1
 16        WHERE  empno = TREAT(nt_emp(i) AS emp_ot).empno;
 17  
 18     DBMS_OUTPUT.PUT_LINE(TO_CHAR(SQL%ROWCOUNT) || ' rows updated.' );
 19 
 20  END;
 21  /

14 rows updated.

PL/SQL procedure successfully completed.

We can see that the TREAT function casts each each element of our nt_emp collection back to the EMP_OT type. In doing so, each attribute of the object is available for direct reference.

In terms of performance, the TREAT workaround compares well. In initial tests, it was as quick with 50,000 records as the fastest bulk update possible when using a cast collection (either a complex view update using the BYPASS_UJVC hint or a MERGE).

acknowledgements

The workaround that uses objects and the TREAT function is attributable in its entirety to James Padfield, who discovered it by his own experimentation. James has allowed me to write it up for this article.

source code

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

Adrian Billington, July 2005 (updated June 2009)

oracle-developer.net