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.
further reading
Several references to oracle-developer.net articles have been provided throughout this article. To recap, workarounds to PLS-00436 in previous versions of Oracle are available here. Workarounds to avoid updated primary key columns with SET ROW are available here. An overview of FORALL is available in this article and additionally in the official PL/SQL Language Reference. The version of RUNSTATS used in this article is available here.
source code
The source code for the examples in this article can be downloaded from here.
Adrian Billington, November 2007
Back to Top