Collections are an interesting lot. They can be one of the most useful tools in the Oracle arsenal, yet they can also be very frustrating to implement. For those unfamiliar with them, a collection/varray is defined as “an ordered group of elements, all of the same type. Each element has a unique subscript that determines its position in the collection.” The definition seems simple enough but it may be deceptively so. To make matters even more confusing, to use a collection you must create a database type for it to reference; a varray requires a type as well but that type can be declared in the PL/SQL block. To clear the air a bit let’s go through some examples of defining and using collections and varrays: The first example uses a collection to store vendor ids and then process them for a report. The code builds two ‘tables’ and compares the contents of them by loading collections and comparing one collection to the other; output is displayed for the conditions of the two tables being equal and the two tables not being equal:
SQL>
SQL> set serveroutput on size 1000000
SQL>
SQL> CREATE OR REPLACE type integer_table is table of integer;
2 /
Type created.
SQL>
SQL> DECLARE
2
3
4 vendor_key_table integer_table;
5 vendor_key_table2 integer_table;
6 CURSOR tst
7 IS
8 SELECT purch_order, SUM (dollars),
9 CAST (COLLECT (TO_NUMBER (vendor_key)) AS integer_table)
10 FROM (SELECT 1 purch_order, 3 dollars, 435235 vendor_key
11 FROM DUAL
12 UNION ALL
13 SELECT 1 purch_order, 8 dollars, 123452 vendor_key
14 FROM DUAL
15 UNION ALL
16 SELECT 2 purch_order, 7 dollars, 433738 vendor_key
17 FROM DUAL
18 UNION ALL
19 SELECT 2 purch_order, 4 dollars, 383738 vendor_key
20 FROM DUAL
21 UNION ALL
22 SELECT 2 purch_order, 5 dollars, 387118 vendor_key
23 FROM DUAL)
24 GROUP BY purch_order;
25
26
27 CURSOR tst2
28 IS
29 SELECT purch_order, SUM (dollars),
30 CAST (COLLECT (TO_NUMBER (vendor_key)) AS integer_table)
31 FROM (SELECT 1 purch_order, 3 dollars, 435235 vendor_key
32 FROM DUAL
33 UNION ALL
34 SELECT 2 purch_order, 4 dollars, 383738 vendor_key
35 FROM DUAL
36 UNION ALL
37 SELECT 2 purch_order, 7 dollars, 433738 vendor_key
38 FROM DUAL
39 UNION ALL
40 SELECT 2 purch_order, 5 dollars, 387118 vendor_key
41 FROM DUAL)
42 GROUP BY purch_order;
43 v_purch_order NUMBER;
44 v_dollars NUMBER;
45
46
47 mystr VARCHAR2 (4000);
48
49
50 v_purch_order2 NUMBER;
51 v_dollars2 NUMBER;
52
53
54 mystr2 VARCHAR2 (4000);
55 BEGIN
56 OPEN tst;
57 open tst2;
58
59
60 LOOP
61 mystr := NULL;
62 mystr2 := NULL;
63
64
65 FETCH tst
66 INTO v_purch_order, v_dollars, vendor_key_table;
67
68
69 FETCH tst2
70 INTO v_purch_order2, v_dollars2, vendor_key_table2;
71
72
73 IF tst%NOTFOUND
74 THEN
75 EXIT;
76 END IF;
77
78
79 if vendor_key_table = vendor_key_table2 then
80 dbms_output.put_line('equal');
81 else
82 dbms_output.put_line(' not equal');
83 end if;
84
85
86 -- loop through the collection and build a string so that
87 -- we can display it and prove that it works
88 FOR cur1 IN (SELECT COLUMN_VALUE vendor_key
89 FROM TABLE (vendor_key_table))
90 LOOP
91 mystr := mystr || ',' || cur1.vendor_key;
92 -- /* based on the value of the sum, you can do something with each detail*/
93 -- if v_dollars > 12 then
94 -- UPDATE VENDOR SET paid_status = 'P' where vendor_key = cur1.vendor_key;
95 -- end if;
96 END LOOP;
97
98
99 DBMS_OUTPUT.put_line ( 'Purchase Order-> '
100 || TO_CHAR (v_purch_order)
101 || ' dollar total-> '
102 || TO_CHAR (v_dollars)
103 || ' vendorkey list-> '
104 || SUBSTR (mystr, 2));
105
106
107 -- loop throught the collection and build a string so that
108 -- we can display it and prove that it works
109 FOR cur2 IN (SELECT COLUMN_VALUE vendor_key
110 FROM TABLE (vendor_key_table2))
111 LOOP
112 mystr2 := mystr2 || ',' || cur2.vendor_key;
113 END LOOP;
114
115
116 DBMS_OUTPUT.put_line ( 'Purchase Order-> '
117 || TO_CHAR (v_purch_order2)
118 || ' dollar total-> '
119 || TO_CHAR (v_dollars2)
120 || ' vendorkey list-> '
121 || SUBSTR (mystr2, 2));
122
123
124 END LOOP;
125 CLOSE tst;
126 CLOSE tst2;
127 END;
128 /
not equal
Purchase Order-> 1 dollar total-> 11 vendorkey list-> 435235,123452
Purchase Order-> 1 dollar total-> 3 vendorkey list-> 435235
equal
Purchase Order-> 2 dollar total-> 16 vendorkey list-> 433738,387118,383738
Purchase Order-> 2 dollar total-> 16 vendorkey list-> 383738,387118,433738
PL/SQL procedure successfully completed.
SQL>
Comparing the collections rather than looping through each table makes the work much easier to complete. Notice we only needed one type created; the same type satisfied the conditions for both collection tables.
The next example shows how things can go astray with the bulk collect operation when the limit does not evenly divide the result set. In the first part of the example we use the well-known ‘exit when cursor%notfound;’ directive with less than stellar results (we miss inserting 5 records into the second table); the second part of the example shows how to properly implement an exit from a bulk collect operation; this one uses a varray:
SQL>
SQL> set echo on linesize 150 trimspool on
SQL>
SQL> create table emp_test as select * From emp where 0=1;
Table created.
SQL>
SQL> declare
2 type empcoltyp is table of emp%rowtype;
3 emp_c empcoltyp;
4
5 cursor get_emp_data is
6 select * from emp;
7
8 begin
9 open get_emp_data;
10 loop
11 fetch get_emp_data bulk collect into emp_c limit 9;
12 exit when get_emp_data%notfound;
13
14 for i in 1..emp_c.count loop
15 insert into emp_test (empno, ename, sal)
16 values (emp_c(i).empno, emp_c(i).ename, emp_c(i).sal);
17 end loop;
18
19 end loop;
20
21 commit;
22
23 end;
24 /
PL/SQL procedure successfully completed.
SQL>
SQL> select * from emp_test;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH 800
7499 ALLEN 1600
7521 WARD 1250
7566 JONES 2975
7654 MARTIN 1250
7698 BLAKE 2850
7782 CLARK 2450
7788 SCOTT 3000
7839 KING 5000
9 rows selected.
SQL>
SQL> truncate table emp_test;
Table truncated.
SQL>
SQL> declare
2 type empcoltyp is table of emp%rowtype;
3 emp_c empcoltyp;
4
5 cursor get_emp_data is
6 select * from emp;
7
8 begin
9 open get_emp_data;
10 loop
11 fetch get_emp_data bulk collect into emp_c limit 9;
12 exit when emp_c.count = 0;
13
14 for i in 1..emp_c.count loop
15 insert into emp_test (empno, ename, sal)
16 values (emp_c(i).empno, emp_c(i).ename, emp_c(i).sal);
17 end loop;
18
19 end loop;
20
21 commit;
22
23 end;
24 /
PL/SQL procedure successfully completed.
SQL>
SQL> select * from emp_test;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH 800
7499 ALLEN 1600
7521 WARD 1250
7566 JONES 2975
7654 MARTIN 1250
7698 BLAKE 2850
7782 CLARK 2450
7788 SCOTT 3000
7839 KING 5000
7844 TURNER 1500
7876 ADAMS 1100
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7900 JAMES 950
7902 FORD 3000
7934 MILLER 1300
14 rows selected.
SQL>
What happened in the first part? Since the limit was more than the number of remaining records the NOTFOUND indicator was set at the end of the fetch. We had 5 records left to process in the varray but the ‘exit when cursor%notfound;’ statement terminated the loop BEFORE we could get the remaining 5 records inserted into our table, thus they were lost. Using the ‘exit when collection.count = 0;’ construct prevents us from missing records since the count was greater than 0 even when the NOTFOUND indicator was set. This allows us to process the remaining records in the varray before exiting the loop. [Yes, the exit could be coded at the end of the loop rather than the beginning and the ‘exit when cursor%NOTFOUND;’ would process the remaining records but that, to me, defeats the purpose of the conditional exit. As I see it, we want to exit the loop when no more work is to be done, not look for partial sets of data to apply then exit before the next (unsuccessful) fetch.] Our next example does two things: loads data using bulk collect into a varray then uses the FORALL loop construct to quickly process the collection and insert the data into a staging table (I believe this originally appeared in ‘Morgan’s Library’ on the psoug.org website). The second part is a bit contrived as it uses a collection to process deletes from a table — deletes that could have easily been executed with a single SQL statement — but it does show the power of using collections and varrays:
SQL>
SQL> set echo on timing on
SQL>
SQL> create table temp_stg(
2 usrname varchar2(30),
3 usrjob varchar2(20),
4 usrsal number
5 );
Table created.
SQL>
SQL> begin
2 for i in 1..1000000 loop
3 insert into temp_stg
4 values ('Blorp'||i, 'Job'||i, 1200*(mod(i,3)));
5 end loop;
6
7 commit;
8
9 end;
10 /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> create table temp_ld(
2 usrname varchar2(30),
3 usrjob varchar2(20),
4 usrsal number
5 );
Table created.
SQL>
SQL> CREATE OR REPLACE PROCEDURE bulk_load IS
2
3 TYPE dfarray IS TABLE OF temp_stg%ROWTYPE;
4 ld_dat dfarray;
5
6 CURSOR stg IS
7 SELECT *
8 FROM temp_stg;
9
10 BEGIN
11 OPEN stg;
12 LOOP
13 FETCH stg BULK COLLECT INTO ld_dat LIMIT 1000;
14
15 FORALL i IN 1..ld_dat.COUNT
16 INSERT INTO temp_ld VALUES ld_dat(i);
17
18 EXIT WHEN ld_dat.count=0;
19 END LOOP;
20 COMMIT;
21 CLOSE r;
22 END bulk_load;
23 /
Procedure created.
SQL>
SQL> exec bulk_load;
PL/SQL procedure successfully completed.
SQL>
SQL> select * from temp_stg where usrname like '%99999%';
USRNAME USRJOB USRSAL
------------------------------ -------------------- ----------
Blorp99999 Job99999 0
Blorp199999 Job199999 1200
Blorp299999 Job299999 2400
Blorp399999 Job399999 0
Blorp499999 Job499999 1200
Blorp599999 Job599999 2400
Blorp699999 Job699999 0
Blorp799999 Job799999 1200
Blorp899999 Job899999 2400
Blorp999990 Job999990 0
Blorp999991 Job999991 1200
USRNAME USRJOB USRSAL
------------------------------ -------------------- ----------
Blorp999992 Job999992 2400
Blorp999993 Job999993 0
Blorp999994 Job999994 1200
Blorp999995 Job999995 2400
Blorp999996 Job999996 0
Blorp999997 Job999997 1200
Blorp999998 Job999998 2400
Blorp999999 Job999999 0
19 rows selected.
SQL>
Trust me that the data loads took very little time to process. Here is the contrived part, but it is still a good example of the power of using collections:
SQL> CREATE OR REPLACE PROCEDURE data_del IS
2
3 TYPE dfarray IS TABLE OF temp_stg.usrname%TYPE;
4 ld_dat dfarray;
5
6 CURSOR stg IS
7 SELECT usrname
8 FROM temp_stg
9 where usrname like '%9999%';
10
11 BEGIN
12 OPEN stg;
13 LOOP
14 FETCH stg BULK COLLECT INTO ld_dat LIMIT 1000;
15
16 FORALL i IN 1..ld_dat.COUNT
17 delete from temp_ld where usrname = ld_dat(i);
18
19 EXIT WHEN ld_dat.count=0;
20 END LOOP;
21 COMMIT;
22 CLOSE stg;
23 END data_del;
24 /
Procedure created.
SQL>
SQL> show errors
No errors.
SQL>
SQL> exec data_del;
PL/SQL procedure successfully completed.
SQL>
SQL> select * from temp_ld where usrname like '%99999%';
no rows selected
SQL>
Collections can be a real timesaver for bulk processing of data; they may not be applicable in every situation but when the conditions are right they can make your job so much easier. Master collections and varrays and you may be able to amaze your friends.
