avoiding pls-00436 with forall
I'm pleased to announce that I've been selected as a finalist in the inaugural Oracle Database Developer Choice Awards. To quote, these awards "celebrate and recognize technical expertise and contributions in the Oracle Database community".
I've been selected for the PL/SQL category, primarily for the articles and utilities that I share here on oracle-developer.net. I'm very pleased to be recognised by the Oracle Community in this way and if you have found oracle-developer.net to be a helpful and/or interesting resource, then I'd appreciate your vote (my voting page is here).
More information on the Awards, including an explanatory video, the rules, other categories and finalists can all be found on the main page.
Thanks for your support!
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.
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 /
SQL> CREATE TYPE emp_ntt AS TABLE OF emp_ot; 2 /
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).
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.
The source code for the examples in this article can be downloaded from here.
Adrian Billington, July 2005 (updated June 2009)Back to Top