INDICES OF: The INDICES OF clause allows us to load non-contiguous (sparse) arrays by telling Oracle to use just the elements that are populated. Remember in versions prior to 10g that arrays had to be dense and we would use iterators such as [array.FIRST .. array.LAST] or [1 .. array.COUNT] to address them (these are still syntactically valid of course). The INDICES OF clause is simple to use as seen in the example below.
SQL> DECLARE
2
3 TYPE aat_rec IS TABLE OF tgt%ROWTYPE
4 INDEX BY PLS_INTEGER;
5 aa_recs aat_rec;
6
7 BEGIN
8
9 /* Load a sparse array... */
10 SELECT object_id, object_name BULK COLLECT INTO aa_recs
11 FROM all_objects
12 WHERE ROWNUM <= 10;
13
14 aa_recs.DELETE(2);
15 aa_recs.DELETE(4);
16 aa_recs.DELETE(6);
17
18 /* Load table using sparse array... */
19 FORALL i IN INDICES OF aa_recs
20 INSERT INTO tgt
21 VALUES aa_recs(i);
22
23 DBMS_OUTPUT.PUT_LINE(
24 TO_CHAR(SQL%ROWCOUNT) || ' rows inserted.'
25 );
26
27 END;
28 /
7 rows inserted.
PL/SQL procedure successfully completed.
Note that the array used in the INDICES OF clause does not necessarily have to be the one that is being loaded. Like all versions of FORALL, it is simply a driver to tell Oracle the indices to use in any arrays referenced in the subsequent DML statement. We can demonstrate this quite easily as follows.
SQL> DECLARE
2
3 TYPE aat_rec IS TABLE OF tgt%ROWTYPE
4 INDEX BY PLS_INTEGER;
5 aa_recs aat_rec;
6
7 TYPE aat_id IS TABLE OF PLS_INTEGER
8 INDEX BY PLS_INTEGER;
9 aa_ids aat_id;
10
11 BEGIN
12
13 /* Populate an array with data to be loaded... */
14 SELECT object_id, object_name BULK COLLECT INTO aa_recs
15 FROM all_objects
16 WHERE ROWNUM <= 10;
17
18 /* Set the "driving array" indices... */
19 aa_ids(1) := NULL; --value of element is irrelevant
20 aa_ids(3) := NULL;
21 aa_ids(5) := NULL;
22
23 /* Load table using sparse ID array as driver... */
24 FORALL i IN INDICES OF aa_ids
25 INSERT INTO tgt
26 VALUES aa_recs(i);
27
28 DBMS_OUTPUT.PUT_LINE(
29 TO_CHAR(SQL%ROWCOUNT) || ' rows inserted.'
30 );
31
32 END;
33 /
3 rows inserted.
PL/SQL procedure successfully completed.
Note that in the previous example, we were only interested in the indices of the driving array. The values within each element were irrelevant; so irrelevant in fact, that we didn't even give them a value.
VALUES OF:
The VALUES OF enables us to load just the elements of a data array where the indices match the values of a driving array (i.e. the data within the elements). In all probability this will be used far less than the INDICES OF clause, but it is worth covering here. The following example shows how we might load from the values within the elements of a driving array.
SQL> DECLARE
2
3 TYPE aat_id IS TABLE OF PLS_INTEGER
4 INDEX BY PLS_INTEGER;
5 aa_ids aat_id;
6
7 TYPE aat_rec IS TABLE OF tgt%ROWTYPE
8 INDEX BY PLS_INTEGER;
9 aa_recs aat_rec;
10
11 BEGIN
12
13 /*
14 * Load up the "driving" array to say which
15 * records from main array we want to use...
16 */
17 aa_ids(1) := 3;
18 aa_ids(2) := 8;
19 aa_ids(3) := 10;
20
21 /* Load up some records into our array... */
22 SELECT ROWNUM, object_name BULK COLLECT INTO aa_recs
23 FROM all_objects
24 WHERE ROWNUM <= 20;
25
26 /*
27 * Load table using indices determined from the VALUES of "driving"
28 * array...
29 */
30 FORALL i IN VALUES OF aa_ids
31 INSERT INTO tgt
32 VALUES aa_recs(i);
33
34 DBMS_OUTPUT.PUT_LINE(
35 TO_CHAR(SQL%ROWCOUNT) || ' rows inserted.'
36 );
37
38 END;
39 /
3 rows inserted.
PL/SQL procedure successfully completed.
Now we can look at the data in the table to see that it is elements 3,8,10 that were loaded (i.e. the values in our driving array) and not the elements 1,2,3 (i.e. the indices of the driving array elements). We can see this because we fetched ROWNUM into the ID attribute of the data array.
SQL> SELECT * FROM tgt;
ID VAL
---------- -----------------------------------
3 SYSTEM_PRIVILEGE_MAP
8 STMT_AUDIT_OPTION_MAP
10 RE$NV_LIST
3 rows selected.
EXCEPTIONS:
The following is a small example of the exception we can expect if an index or value in the "driving" array doesn't exist in the "data" array. We'll contrive a driving array with an index beyond the bounds of the data array and then try to load our target table.
SQL> DECLARE
2
3 TYPE aat_rec IS TABLE OF tgt%ROWTYPE
4 INDEX BY PLS_INTEGER;
5 aa_recs aat_rec;
6
7 TYPE aat_id IS TABLE OF PLS_INTEGER
8 INDEX BY PLS_INTEGER;
9 aa_ids aat_id;
10
11 BEGIN
12
13 /* Load some data... */
14 SELECT object_id, object_name BULK COLLECT INTO aa_recs
15 FROM all_objects
16 WHERE ROWNUM <= 10;
17
18 /* Set up a reference array with high key and value... */
19 aa_ids(100) := NULL; --value irrelevant here
20
21 /* Now try to load... */
22 FORALL i IN INDICES OF aa_ids
23 INSERT INTO tgt
24 VALUES aa_recs(i);
25
26 EXCEPTION
27 WHEN OTHERS THEN
28 DBMS_OUTPUT.PUT_LINE(SQLERRM);
29 END;
30 /
ORA-22160: element at index [100] does not exist
PL/SQL procedure successfully completed.
Usefully, Oracle tells us the index it was trying to reference. The same exception and output would also be raised if we were to use the VALUES OF clause.
No comments:
Post a Comment