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 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).
No comments:
Post a Comment