collection extensions in 10g
This article covers a range of new set operators, functions and conditions introduced in 10g. Briefly, these extensions enable us to work more efficiently and elegantly with collections, both in SQL and PL/SQL. Collections, based on user-defined types, have been available in Oracle since version 8 (note that a collection is a SQL type, not a PL/SQL table). They offer enormous potential for extending the PL/SQL language and there are many examples of how to use them available on the web and in forums.
In this article we will be looking at examples of the following:
- Collection comparisons;
- Collection functions such as SET, CARDINALITY and POWERMULTISET;
- Collection conditions such as MEMBER and SUBMULTISET; and
- Collection operators such as MULTISET EXCEPT and MULTISET UNION.
Note that most of the examples will be in PL/SQL. This is not because the new operators cannot be used in SQL (they are all primarily SQL extensions in fact) - it is because it is easier to see the results of the operators with a few DBMS_OUTPUT statements.
setup
First we'll create a simple collection type to use in the examples.
SQL> CREATE OR REPLACE TYPE varchar2_ntt AS TABLE OF VARCHAR2(4000); 2 /
Type created.
comparing collections
Prior to 10g, if we wished to compare the contents of one collection with another, we'd have to resort to looping through the collections in PL/SQL or using SQL techniques (such as TABLE() and MINUS for example). Now, however, collections can be compared for equality as any "regular" datatype (note that to compare collections of complex types, a MAP method is required. For an example, see the section on complex collection types at the end of this article). In the following example, we'll initialise three collections, two of which are equal, and then test them for equality under a range of scenarios.
SQL> DECLARE
2
3 nt0 varchar2_ntt := varchar2_ntt('A','B','C');
4 nt1 varchar2_ntt := varchar2_ntt('C','D','E');
5 nt2 varchar2_ntt := varchar2_ntt('C','D','E');
6
7 PROCEDURE compare (
8 test_in IN VARCHAR2,
9 ntl_in IN varchar2_ntt,
10 ntr_in IN varchar2_ntt
11 ) IS
12 BEGIN
13 DBMS_OUTPUT.PUT(RPAD('['||test_in||']',30) || '=> ');
14 IF ntl_in = ntr_in THEN
15 DBMS_OUTPUT.PUT_LINE( 'EQUAL' );
16 ELSE
17 DBMS_OUTPUT.PUT_LINE( 'NOT EQUAL' );
18 END IF;
19 END compare;
20
21 BEGIN
22
23 /* Compare populated collections... */
24 compare( '1: nt0->nt1, populated', nt0, nt1 );
25 compare( '2: nt1->nt2, populated', nt1, nt2 );
26
27 /* Compare collections after deletion... */
28 nt0.DELETE;
29 nt1.DELETE;
30 compare( '3: nt0->nt1, deleted', nt0, nt1 );
31
32 /* Compare newly initialised collections... */
33 nt0 := varchar2_ntt();
34 nt1 := varchar2_ntt();
35 compare( '4: nt0->nt1, initialised', nt0, nt1 );
36
37 END;
38 /
[1: nt0->nt1, populated] => NOT EQUAL [2: nt1->nt2, populated] => EQUAL [3: nt0->nt1, deleted] => EQUAL [4: nt0->nt1, initialised] => EQUAL PL/SQL procedure successfully completed.
Equality tests for collections in 10g also include the use of IN (which after all is just a convenient method of bundling many similar equality-OR tests into a shorter syntax). We can see this below. Note that the order of the elements is irrelevant. In the following example, collections nt1 and nt2 are equivalent, which is why the IN test returns TRUE.
SQL> DECLARE
2 nt0 varchar2_ntt := varchar2_ntt('A','B','C');
3 nt1 varchar2_ntt := varchar2_ntt('C','D','E');
4 nt2 varchar2_ntt := varchar2_ntt('E','C','D');
5 BEGIN
6 IF nt2 IN ( nt0, nt1 ) THEN
7 DBMS_OUTPUT.PUT_LINE( 'Collection nt2 is equal to at least one other.' );
8 END IF;
9 END;
10 /
Collection nt2 is equal to at least one other. PL/SQL procedure successfully completed.
cardinality
In PL/SQL, it has always been trivial to get a count of elements in an array or collection, using the COUNT pseudo-method. The new CARDINALITY function in 10g now makes it trivial to count the elements of a collection in SQL. This function is logically equivalent to the COUNT pseudo-method. In the following examples we'll show simple examples of the function's usage in both PL/SQL and SQL.
SQL> DECLARE 2 nt varchar2_ntt := varchar2_ntt( 'A','B','C','C' ); 3 BEGIN 4 DBMS_OUTPUT.PUT_LINE( 'Count = ' || nt.COUNT ); 5 DBMS_OUTPUT.PUT_LINE( 'Cardinality = ' || CARDINALITY(nt) ); 6 END; 7 /
Count = 4 Cardinality = 4 PL/SQL procedure successfully completed.
This time we can see the CARDINALITY function being used in SQL whereas the COUNT method cannot.
SQL> SELECT CARDINALITY(nt)
2 FROM (
3 SELECT varchar2_ntt('A','B','C','C') AS nt
4 FROM dual
5 );
CARDINALITY(NT)
---------------
4
1 row selected.
SQL> SELECT nt.COUNT
2 FROM (
3 SELECT varchar2_ntt('A','B','C','C') AS nt
4 FROM dual
5 );
SELECT nt.COUNT
*
ERROR at line 1:
ORA-00904: "NT"."COUNT": invalid identifier
The fact that the CARDINALITY function is available to us in SQL means that we can also use it in filter predicates.
SQL> SELECT *
2 FROM (
3 SELECT varchar2_ntt('A','B') AS nt
4 FROM dual
5 UNION ALL
6 SELECT varchar2_ntt('A','B','C','D') AS nt
7 FROM dual
8 )
9 WHERE CARDINALITY(nt) > 2;
NT
-----------------------------------
VARCHAR2_NTT('A', 'B', 'C', 'D')
1 row selected.
set
The new SET function in 10g returns a distinct range of elements from a collection (i.e. DISTINCT). For simple collections such as the one we have used throughout this article, it's very straightforward, as the following example demonstrates.
SQL> SELECT nt
2 , SET(nt) AS nt_set
3 FROM (
4 SELECT varchar2_ntt('A','B','C','C') AS nt
5 FROM dual
6 );
NT NT_SET
----------------------------------- ------------------------------
VARCHAR2_NTT('A', 'B', 'C', 'C') VARCHAR2_NTT('A', 'B', 'C')
1 row selected.
SQL> SELECT nt.column_value AS distinct_element
2 FROM TABLE(SET(varchar2_ntt('A','B','C','C'))) nt;
DISTINCT_ELEMENT ---------------- A B C 3 rows selected.
is (not) a set
In addition to turning collections into valid sets, we can also test to see whether a collection is already a set, as the following example shows. Note that a restriction with this condition is that the collection must be initialised and not NULL.
SQL> DECLARE
2
3 nt_null varchar2_ntt;
4 nt_initialised varchar2_ntt := varchar2_ntt();
5 nt_set varchar2_ntt := varchar2_ntt('A','B','C');
6 nt_not_set varchar2_ntt := varchar2_ntt('A','B','C','C');
7
8 PROCEDURE test (
9 test_in IN VARCHAR2,
10 result_in IN BOOLEAN
11 ) IS
12 BEGIN
13 DBMS_OUTPUT.PUT( RPAD('[' || test_in || ']',30) || '=> ');
14 DBMS_OUTPUT.PUT_LINE(CASE result_in
15 WHEN TRUE
16 THEN 'TRUE'
17 WHEN FALSE
18 THEN 'FALSE'
19 ELSE 'NULL'
20 END);
21 END test;
22
23 BEGIN
24
25 /* First test the NULL collection... */
26 test( 'nt_null IS A SET', nt_null IS A SET );
27 test( 'nt_null IS NOT A SET', nt_null IS NOT A SET );
28
29 /* Now test the valid collections... */
30 test( 'nt_initialised IS A SET', nt_initialised IS A SET );
31 test( 'nt_set IS A SET', nt_set IS A SET );
32 test( 'nt_not_set IS A SET', nt_not_set IS A SET );
33 test( 'SET(nt_not_set) IS A SET', SET(nt_not_set) IS A SET );
34
35 END;
36 /
[nt_null IS A SET] => NULL [nt_null IS NOT A SET] => NULL [nt_initialised IS A SET] => TRUE [nt_set IS A SET] => TRUE [nt_not_set IS A SET] => FALSE [SET(nt_not_set) IS A SET] => TRUE PL/SQL procedure successfully completed.
cardinality revisited
We have already seen the new CARDINALITY function above. The term "cardinality" can sometimes be ambiguous in Oracle. It is sometimes used to describe the count of a domain of values (for example, when describing bitmap indexes) but most of the time it is used to describe a straight count (think CBO). If we wish to get the true cardinality of a collection, we can combine CARDINALITY and SET (for SQL old-timers, this is the logical equivalent of COUNT(DISTINCT)). We can demonstrate this easily as follows.
SQL> DECLARE 2 nt varchar2_ntt := varchar2_ntt( 'A','B','C','C' ); 3 BEGIN 4 DBMS_OUTPUT.PUT_LINE( 'Cardinality = ' || CARDINALITY( nt ) ); 5 DBMS_OUTPUT.PUT_LINE( 'True cardinality = ' || CARDINALITY( SET( nt ) ) ); 6 END; 7 /
Cardinality = 4 True cardinality = 3 PL/SQL procedure successfully completed.
is (not) empty
As we saw with CARDINALITY, we can now explicitly test for collections with a specific number of elements. We can also test for empty collections using the IS (NOT) EMPTY condition. As with the IS (NOT) A SET examples earlier in this article, the restriction on NULL collections applies. This is demonstrated below (remember that these are available in SQL).
SQL> DECLARE
2
3 nt_null varchar2_ntt;
4 nt_initialised varchar2_ntt := varchar2_ntt();
5 nt_populated varchar2_ntt := varchar2_ntt('A','B','C');
6
7 PROCEDURE test (
8 test_in IN VARCHAR2,
9 result_in IN BOOLEAN
10 ) IS
11 BEGIN
12 DBMS_OUTPUT.PUT( RPAD('[' || test_in || ']',40) || '=> ');
13 DBMS_OUTPUT.PUT_LINE(CASE
14 WHEN result_in
15 THEN 'TRUE'
16 ELSE 'FALSE'
17 END);
18 END test;
19
20 BEGIN
21
22 /* First test the NULL collection... */
23 test( 'nt_null IS EMPTY', nt_null IS EMPTY );
24 test( 'nt_null IS NOT EMPTY', nt_null IS NOT EMPTY );
25
26
27 /* Test the valid collections... */
28 test( 'nt_initialised IS EMPTY', nt_initialised IS EMPTY );
29 test( 'nt_populated IS NOT EMPTY', nt_populated IS NOT EMPTY );
30
31 /* Delete the populated collection and repeat test for emptiness... */
32 nt_populated.DELETE;
33 test( 'nt_populated (deleted) IS NOT EMPTY', nt_populated IS NOT EMPTY );
34
35 END;
36 /
[nt_null IS EMPTY] => FALSE [nt_null IS NOT EMPTY] => FALSE [nt_initialised IS EMPTY] => TRUE [nt_populated IS NOT EMPTY] => TRUE [nt_populated (deleted) IS NOT EMPTY] => FALSE PL/SQL procedure successfully completed.
We can see from the example that a NULL collection is neither empty nor not empty. This is something to consider when you pass potentially-NULL collections around as parameters for use in IF/CASE tests or SQL predicates.
submultiset conditions
Submultiset conditions belong to the IN family of SQL conditions and enable us to compare a collection or expression against a subset of another collection (a logical equivalent of "IF collection_A/expression_A IN (elements of collection_B)". The MEMBER OF condition determines whether a literal (or bind variable) exists within a collection and SUBMULTISET OF compares two collections. The following example demonstrates both methods for submultiset comparison.
SQL> DECLARE
2
3 nt1 varchar2_ntt := varchar2_ntt( 'C','D','E' );
4 nt2 varchar2_ntt := varchar2_ntt( 'C','D','E','E' );
5 nt3 varchar2_ntt := varchar2_ntt( 'A','B','C' );
6
7 PROCEDURE test (
8 test_in IN VARCHAR2,
9 result_in IN BOOLEAN
10 ) IS
11 BEGIN
12 DBMS_OUTPUT.PUT( RPAD('[' || test_in || ']',30) || '=> ');
13 DBMS_OUTPUT.PUT_LINE(CASE
14 WHEN result_in
15 THEN 'TRUE'
16 ELSE 'FALSE'
17 END);
18 END test;
19
20 BEGIN
21
22 /* SUBMULTISET tests... */
23 test( 'nt1 SUBMULTISET OF nt2', nt1 SUBMULTISET OF nt2 );
24 test( 'nt2 SUBMULTISET OF nt3', nt2 SUBMULTISET OF nt3 );
25 test( 'nt2 NOT SUBMULTISET OF nt3', nt2 NOT SUBMULTISET OF nt3 );
26
27 /* MEMBER OF tests... */
28 test( 'D MEMBER OF nt1', 'D' MEMBER OF nt1 );
29 test( 'F MEMBER OF nt3', 'F' MEMBER OF nt3 );
30
31 END;
32 /
[nt1 SUBMULTISET OF nt2] => TRUE [nt2 SUBMULTISET OF nt3] => FALSE [nt2 NOT SUBMULTISET OF nt3] => TRUE [D MEMBER OF nt1] => TRUE [F MEMBER OF nt3] => FALSE PL/SQL procedure successfully completed.
multiset operators
Oracle 10g has introduced a number of MULTISET operators. These combine two or more collections of the same type and return a collection that satisfies the operator. In high-level terms, these can largely be considered as equivalent to the SQL SET operators (MINUS, UNION [ALL], INTERSECT), but limited to collections rather than sets of data resulting from SQL queries. The MULTISET operators are listed below with their SQL equivalents where applicable.
| Multiset Operator | SQL Set Equivalent |
| MULTISET UNION DISTINCT | UNION |
| MULTISET UNION | UNION ALL |
| MULTISET UNION ALL | UNION ALL |
| MULTISET INTERSECT | INTERSECT |
| MULTISET INTERSECT DISTINCT | N/A |
| MULTISET EXCEPT | MINUS |
| MULTISET EXCEPT DISTINCT | N/A |
The following PL/SQL block demonstrates each of the MULTISET operators by displaying the resulting collection from each operation.
SQL> DECLARE
2
3 nt0 varchar2_ntt := varchar2_ntt('A','B','C','C');
4 nt1 varchar2_ntt := varchar2_ntt('C','C','D','E','E');
5
6 PROCEDURE display (
7 type_in IN VARCHAR2,
8 nt_in IN varchar2_ntt
9 ) IS
10 i PLS_INTEGER;
11 BEGIN
12 DBMS_OUTPUT.PUT( CHR(10) || RPAD(type_in,50,'.') );
13 i := nt_in.FIRST;
14 WHILE i IS NOT NULL LOOP
15 DBMS_OUTPUT.PUT( nt_in(i) || ' ' );
16 i := nt_in.NEXT(i);
17 END LOOP;
18 DBMS_OUTPUT.NEW_LINE;
19 END display;
20
21 BEGIN
22
23 display('MULTISET UNION',
24 nt0 MULTISET UNION nt1 );
25
26 display('MULTISET UNION ALL (SAME AS MULTISET UNION)',
27 nt0 MULTISET UNION ALL nt1 );
28
29 display('MULTISET UNION DISTINCT',
30 nt0 MULTISET UNION DISTINCT nt1 );
31
32 display('MULTISET EXCEPT (nt0 -> nt1)',
33 nt0 MULTISET EXCEPT nt1 );
34
35 display('MULTISET EXCEPT (nt1 -> nt0)',
36 nt1 MULTISET EXCEPT nt0 );
37
38 display('MULTISET EXCEPT DISTINCT (nt1 -> nt0)',
39 nt1 MULTISET EXCEPT DISTINCT nt0 );
40
41 display('MULTISET INTERSECT',
42 nt0 MULTISET INTERSECT nt1 );
43
44 display('MULTISET INSERSECT DISTINCT',
45 nt0 MULTISET INTERSECT DISTINCT nt1 );
46
47 display('SET OF AN INTERSECT (SAME AS INTERSECT DISTINCT)',
48 SET( nt0 MULTISET INTERSECT nt1 ) );
49
50 END;
51 /
MULTISET UNION....................................A B C C C C D E E MULTISET UNION ALL (SAME AS MULTISET UNION).......A B C C C C D E E MULTISET UNION DISTINCT...........................A B C D E MULTISET EXCEPT (nt0 -> nt1)......................A B MULTISET EXCEPT (nt1 -> nt0)......................D E E MULTISET EXCEPT DISTINCT (nt1 -> nt0).............D E MULTISET INTERSECT................................C C MULTISET INSERSECT DISTINCT.......................C SET OF AN INTERSECT (SAME AS INTERSECT DISTINCT)..C PL/SQL procedure successfully completed.
The following examples simply demonstrate that MULTISETs are available in SQL.
SQL> SELECT *
2 FROM TABLE( varchar2_ntt('A','B','C')
3 MULTISET UNION DISTINCT
4 varchar2_ntt('B','C','D') );
COLUMN_VALUE ------------------------------ A B C D 4 rows selected.
SQL> SELECT varchar2_ntt('A','B','C')
2 MULTISET EXCEPT
3 varchar2_ntt('B','C','D') AS multiset_except
4 FROM dual;
MULTISET_EXCEPT
------------------------------
VARCHAR2_NTT('A')
1 row selected.
powermultiset functions
For the completists amongst us, the POWERMULTISET functions enable us to create a range of collections from a single input collection. Unlike all other new features in this article, the POWERMULTISET functions are available in SQL only. There are two functions in this "family", as follows.
- POWERMULTISET
- POWERMULTISET_BY_CARDINALITY
The POWERMULTISET function takes an input collection as an argument and returns a "collection of collections" that represent every unique multiset of data possible from the input. Clear?!? It can be quite difficult to imagine at first, but suppose we have a collection comprising elements (A,B). There are three unique multisets of data possible from this: (A), (B), (A,B). Given this input, therefore, POWERMULTISET would return a collection with three elements; where each element is itself a collection.
Throughout this article, we've used a simple collection type of VARCHAR2(4000) to represent our individual sets of data. For POWERMULTISET to return a collection of these multisets from a given input, we must create a new type "over the top of it" (a TYPE of TYPE, if you like). This is quite simple as the following demonstrates.
SQL> CREATE TYPE varchar2_ntts AS TABLE OF varchar2_ntt; 2 /
Type created.
We now have a collection type that represents multiple instances of our original collection type. We can now demonstrate POWERMULTISET, as follows. To keep the output short, we'll return a collection of all multisets available from an input collection of just three elements.
SQL> SELECT CAST(
2 POWERMULTISET(
3 varchar2_ntt('A','B','C')) AS varchar2_ntts) AS pwrmltset
4 FROM dual;
PWRMLTSET
--------------------------------------------------------------------------------
VARCHAR2_NTTS(VARCHAR2_NTT('A'), VARCHAR2_NTT('B'), VARCHAR2_NTT('A', 'B'), VARC
HAR2_NTT('C'), VARCHAR2_NTT('A', 'C'), VARCHAR2_NTT('B', 'C'), VARCHAR2_NTT('A',
'B', 'C'))
1 row selected.
As a collection in SQL, this is probably of little use to most of us. We are more likely to use POWERMULTISET in a TABLE expression, if at all. For this, the "collection type of collection type" (VARCHAR2_NTTS in the above example), is not required. The following example drops the VARCHAR2_NTTS type and demonstrates an alternative invocation of POWERMULTISET.
SQL> DROP TYPE varchar2_ntts;
Type dropped.
SQL> SELECT *
2 FROM TABLE(POWERMULTISET(varchar2_ntt('A','B','C')));
COLUMN_VALUE
------------------------------
VARCHAR2_NTT('A')
VARCHAR2_NTT('B')
VARCHAR2_NTT('A', 'B')
VARCHAR2_NTT('C')
VARCHAR2_NTT('A', 'C')
VARCHAR2_NTT('B', 'C')
VARCHAR2_NTT('A', 'B', 'C')
7 rows selected.
The POWERMULTISET_BY_CARDINALITY function takes this one stage further by enabling us to specify the cardinality of the multisets that we wish to return. In the following example, we'll request all multisets of three elements that can be derived from an input collection of five elements. Note that in this example, we have a duplicate element to demonstrate that the POWERMULTISET functions deal with multisets and not sets (i.e. not distinct values).
SQL> SELECT *
2 FROM TABLE(
3 POWERMULTISET_BY_CARDINALITY(
4 varchar2_ntt('A','B','C','D','D'), 3));
COLUMN_VALUE
------------------------------
VARCHAR2_NTT('A', 'B', 'C')
VARCHAR2_NTT('A', 'B', 'D')
VARCHAR2_NTT('A', 'C', 'D')
VARCHAR2_NTT('B', 'C', 'D')
VARCHAR2_NTT('A', 'B', 'D')
VARCHAR2_NTT('A', 'C', 'D')
VARCHAR2_NTT('B', 'C', 'D')
VARCHAR2_NTT('A', 'D', 'D')
VARCHAR2_NTT('B', 'D', 'D')
VARCHAR2_NTT('C', 'D', 'D')
10 rows selected.
working with complex collections
So far we've been using a simple collection type for all examples. Furthermore, all examples work equally in SQL or PL/SQL. The same is not true for complex collection types (i.e. based on object types with multiple attributes). For complex collections to be compared to other collections or be used with any of the SUBMULTISET conditions, MULTISET operators or POWERMULTISET and SET functions, we must enable their elements to be sorted. We do this by including a single MAP order method in the type definition. In the following example, we'll create a complex type without a MAP order method and see how Oracle resolves the sorting when we call the SET function.
SQL> CREATE TYPE complex_ot AS OBJECT 2 ( attr_one VARCHAR2(10) 3 , attr_two VARCHAR2(10) 4 , attr_three NUMBER ); 5 /
Type created.
SQL> CREATE TYPE complex_ntt AS TABLE OF complex_ot; 2 /
Type created.
First we'll call the SET function in a simple SQL statement.
SQL> SELECT *
2 FROM TABLE(
3 SET( complex_ntt(complex_ot('data', 'more data', 1),
4 complex_ot('data', 'some data', 2),
5 complex_ot('data', 'dupe data', 3),
6 complex_ot('data', 'dupe data', 3)) ));
ATTR_ONE ATTR_TWO ATTR_THREE ---------- ---------- ---------- data more data 1 data some data 2 data dupe data 3 3 rows selected.
Interestingly, we have the correct answer without a MAP method. It appears as though SQL "understands" our complex type. We can now test the same types in a simple PL/SQL block.
SQL> DECLARE
2 nt complex_ntt := complex_ntt(complex_ot('data', 'more data', 1),
3 complex_ot('data', 'some data', 2),
4 complex_ot('data', 'dupe data', 3),
5 complex_ot('data', 'dupe data', 3));
6 BEGIN
7 DBMS_OUTPUT.PUT_LINE( 'Complex collection has ' ||
8 TO_CHAR(CARDINALITY(SET(nt))) ||
9 ' distinct elements.' );
10 END;
11 /
TO_CHAR(CARDINALITY(SET(nt))) ||
*
ERROR at line 8:
ORA-06550: line 8, column 46:
PLS-00306: wrong number or types of arguments in call to 'SET'
ORA-06550: line 7, column 4:
PL/SQL: Statement ignored
In PL/SQL, Oracle cannot determine the SET from the complex collection (although the error message is not particularly helpful in this instance). This time we'll create the requisite MAP method and try again.
SQL> CREATE TYPE complex_ot AS OBJECT 2 ( attr_one VARCHAR2(10) 3 , attr_two VARCHAR2(10) 4 , attr_three NUMBER(3) 5 , MAP MEMBER FUNCTION sort_key RETURN VARCHAR2 ); 6 /
Type created.
SQL> CREATE TYPE BODY complex_ot AS 2 MAP MEMBER FUNCTION sort_key RETURN VARCHAR2 IS 3 BEGIN 4 RETURN LPAD(attr_one,10) || 5 LPAD(attr_two,10) || 6 TO_CHAR(attr_three,'fm000'); 7 END; 8 END; 9 /
Type body created.
SQL> CREATE TYPE complex_ntt AS TABLE OF complex_ot; 2 /
Type created.
SQL> DECLARE
2 nt complex_ntt := complex_ntt(complex_ot('data', 'more data', 1),
3 complex_ot('data', 'some data', 2),
4 complex_ot('data', 'dupe data', 3),
5 complex_ot('data', 'dupe data', 3));
6 BEGIN
7 DBMS_OUTPUT.PUT_LINE( 'Complex collection has ' ||
8 TO_CHAR(CARDINALITY(SET(nt))) ||
9 ' distinct elements.' );
10 END;
11 /
Complex collection has 3 distinct elements. PL/SQL procedure successfully completed.
acknowledgements
Thanks to Svitin Alexey for a correction to my example that demonstrates how a NULL collection cannot be tested with the IS (NOT) A SET condition.
source code
The source code for the examples in this article can be downloaded from here.
Adrian Billington, June 2004
Back to Top