Collections in Oracle PL/SQL

Oracle uses collections in PL/SQL the same way other languages use arrays. Oracle
provides three basic collections, each with an assortment of methods.

 Index-By Tables (Associative Arrays)

 Nested Table Collections
 Varrays Collections
 Assignments and Equality Tests
 Collection Methods
 MULTISET Operators

o MULTISET UNION {ALL | DISTINCT} Operator

o MULTISET EXCEPT {DISTINCT} Operator
o MULTISET INTERSECT {DISTINCT} Operator
 MULTISET Conditions

o IS {NOT} A SET Condition

o IS {NOT} EMPTY Condition
o MEMBER Condition
o SUBMULTISET Condition
 MULTISET Functions

o CARDINALITY Function
o POWERMULTISET Function
o POWERMULTISET_BY_CARDINALITY Function
o SET Function
 Multidimensional Collections

Related articles.

 Associative Arrays in Oracle 9i

 Bulk Binds (BULK COLLECT & FORALL) and Record Processing in Oracle

Index-By Tables (Associative Arrays)

The first type of collection is known as index-by tables. These behave in the same way as
arrays except that have no upper bounds, allowing them to constantly extend. As the
name implies, the collection is indexed using BINARY_INTEGER values, which do not need
to be consecutive. The collection is extended by assigning values to an element using an
index value that does not currently exist.

SET SERVEROUTPUT ON SIZE 1000000

DECLARE
TYPE table_type IS TABLE OF NUMBER(10)
INDEX BY BINARY_INTEGER;

v_tab table_type;
v_idx NUMBER;
BEGIN
-- Initialise the collection.
<< load_loop >>
FOR i IN 1 .. 5 LOOP
v_tab(i) := i;
END LOOP load_loop;

-- Delete the third item of the collection.

v_tab.DELETE(3);

-- Traverse sparse collection

v_idx := v_tab.FIRST;
<< display_loop >>
WHILE v_idx IS NOT NULL LOOP
DBMS_OUTPUT.PUT_LINE('The number ' || v_tab(v_idx));
v_idx := v_tab.NEXT(v_idx);
END LOOP display_loop;
END;
/

The number 1
The number 2
The number 4
The number 5

PL/SQL procedure successfully completed.

SQL>

In Oracle 9i Release 2 these have been renamed to Associative Arrays and can be

indexed by BINARY INTEGER or VARCHAR2.

Nested Table Collections

Nested table collections are an extension of the index-by tables. The main difference
between the two is that nested tables can be stored in a database column but index-by
tables cannot. In addition some DML operations are possible on nested tables when
they are stored in the database. During creation the collection must be dense, having
consecutive subscripts for the elements. Once created elements can be deleted using
the DELETE method to make the collection sparse. The NEXT method overcomes the
problems of traversing sparse collections.

SET SERVEROUTPUT ON SIZE 1000000

DECLARE
TYPE table_type IS TABLE OF NUMBER(10);
v_tab table_type;
v_idx NUMBER;
BEGIN

-- Initialise the collection with two values.

v_tab := table_type(1, 2);

-- Extend the collection with extra values.

<< load_loop >>
FOR i IN 3 .. 5 LOOP
v_tab.extend;
v_tab(v_tab.last) := i;
END LOOP load_loop;

-- Delete the third item of the collection.

v_tab.DELETE(3);

-- Traverse sparse collection

v_idx := v_tab.FIRST;
<< display_loop >>
WHILE v_idx IS NOT NULL LOOP
DBMS_OUTPUT.PUT_LINE('The number ' || v_tab(v_idx));
v_idx := v_tab.NEXT(v_idx);
END LOOP display_loop;
END;
/

The number 1
The number 2
The number 4
The number 5

PL/SQL procedure successfully completed.

SQL>

Varray Collections
A VARRAY is similar to a nested table except you must specifiy an upper bound in the
declaration. Like nested tables they can be stored in the database, but unlike nested
tables individual elements cannot be deleted so they remain dense.
SET SERVEROUTPUT ON SIZE 1000000
DECLARE
TYPE table_type IS VARRAY(5) OF NUMBER(10);
v_tab table_type;
v_idx NUMBER;
BEGIN
-- Initialise the collection with two values.
v_tab := table_type(1, 2);

-- Extend the collection with extra values.

<< load_loop >>
FOR i IN 3 .. 5 LOOP
v_tab.extend;
v_tab(v_tab.last) := i;
END LOOP load_loop;

-- Can't delete from a VARRAY.

-- v_tab.DELETE(3);

-- Traverse collection
v_idx := v_tab.FIRST;
<< display_loop >>
WHILE v_idx IS NOT NULL LOOP
DBMS_OUTPUT.PUT_LINE('The number ' || v_tab(v_idx));
v_idx := v_tab.NEXT(v_idx);
END LOOP display_loop;
END;
/

The number 1
The number 2
The number 3
The number 4
The number 5

PL/SQL procedure successfully completed.

SQL>

Extending the load_loop to 3..6 attempts to extend the VARRAY beyond it's limit of 5
elements resulting in the following error.

DECLARE
*
ERROR at line 1:
ORA-06532: Subscript outside of limit
ORA-06512: at line 12

Assignments and Equality Tests

Assignments can only be made between collections of the same type. Not types of
similar structures, or with the same name in different packages, but literally the same
type.

The following example shows a successful assignment between two collections of the
same type.

DECLARE
TYPE table_type IS TABLE OF NUMBER(10);
v_tab_1 table_type;
v_tab_2 table_type;
BEGIN
-- Initialise the collection with two values.
v_tab_1 := table_type(1, 2);

-- Assignment works.
v_tab_2 := v_tab_1;
END;
/

PL/SQL procedure successfully completed.

SQL>

If we repeat that, but this time use two separate types with similar definitions, we can
see the code fails to compile due to the illegal assignment.

DECLARE
TYPE table_type_1 IS TABLE OF NUMBER(10);
TYPE table_type_2 IS TABLE OF NUMBER(10);
v_tab_1 table_type_1;
v_tab_2 table_type_2;
BEGIN
-- Initialise the collection with two values.
v_tab_1 := table_type_1(1, 2);

-- Assignment causes compilation error.

v_tab_2 := v_tab_1;
END;
/

v_tab_2 := v_tab_1;
*
ERROR at line 11:
ORA-06550: line 11, column 14:
PLS-00382: expression is of wrong type
ORA-06550: line 11, column 3:
PL/SQL: Statement ignored
SQL>

Collections of the same type can be tested for equality, as shown in the example below.

SET SERVEROUTPUT ON
DECLARE
TYPE table_type IS TABLE OF NUMBER(10);
v_tab_1 table_type;
v_tab_2 table_type;
BEGIN
-- Initialise the collection with two values.
v_tab_1 := table_type(1, 2);
v_tab_2 := v_tab_1;

IF v_tab_1 = v_tab_2 THEN

DBMS_OUTPUT.put_line('1: v_tab_1 = v_tab_2');
END IF;

v_tab_1 := table_type(1, 2, 3);

IF v_tab_1 != v_tab_2 THEN

DBMS_OUTPUT.put_line('2: v_tab_1 != v_tab_2');
END IF;
END;
/
1: v_tab_1 = v_tab_2
2: v_tab_1 != v_tab_2

PL/SQL procedure successfully completed.

SQL>

Collection Methods
A variety of methods exist for collections, but not all are relevant for every collection
type.

 EXISTS(n) - Returns TRUE if the specified element exists.

 COUNT - Returns the number of elements in the collection.
 LIMIT - Returns the maximum number of elements for a VARRAY, or NULL for
nested tables.
 FIRST - Returns the index of the first element in the collection.
 LAST - Returns the index of the last element in the collection.
 PRIOR(n) - Returns the index of the element prior to the specified element.
 NEXT(n) - Returns the index of the next element after the specified element.
  EXTEND - Appends a single null element to the collection.
 EXTEND(n) - Appends n null elements to the collection.
 EXTEND(n1,n2) - Appends n1 copies of the n2th element to the collection.
 TRIM - Removes a single element from the end of the collection.
 TRIM(n) - Removes n elements from the end of the collection.
 DELETE - Removes all elements from the collection.
 DELETE(n) - Removes element n from the collection.
 DELETE(n1,n2) - Removes all elements from n1 to n2 from the collection.

MULTISET Operations

MULTISET UNION {ALL | DISTINCT} Operator

The MULTISET UNION operator joins the two collections together, doing the equivalent of
a UNION ALL between the two sets. The MULTISET UNION and MULTISET UNION
ALL operators are functionally equivalent.

SET SERVEROUTPUT ON
DECLARE
TYPE t_tab IS TABLE OF NUMBER;
l_tab1 t_tab := t_tab(1,2,3,4,5,6);
l_tab2 t_tab := t_tab(5,6,7,8,9,10);
BEGIN
l_tab1 := l_tab1 MULTISET UNION l_tab2;

FOR i IN l_tab1.first .. l_tab1.last LOOP

DBMS_OUTPUT.put_line(l_tab1(i));
END LOOP;
END;
/
1
2
3
4
5
6
5
6
7
8
9
10

PL/SQL procedure successfully completed.

SQL>

The DISTINCT keyword can be added to any of the multiset operations to removes the

duplicates. Adding it to the MULTISET UNION operator makes it the equivalent of
a UNION between the two sets.

SET SERVEROUTPUT ON
DECLARE
TYPE t_tab IS TABLE OF NUMBER;
l_tab1 t_tab := t_tab(1,2,3,4,5,6);
l_tab2 t_tab := t_tab(5,6,7,8,9,10);
BEGIN
l_tab1 := l_tab1 MULTISET UNION DISTINCT l_tab2;

FOR i IN l_tab1.first .. l_tab1.last LOOP

DBMS_OUTPUT.put_line(l_tab1(i));
END LOOP;
END;
/
1
2
3
4
5
6
7
8
9
10

PL/SQL procedure successfully completed.

SQL>

The NOT keyword can be included to get the inverse. For example NOT MULTISET UNION.

MULTISET EXCEPT {DISTINCT} Operator

The MULTISET EXCEPT operator returns the elements of the first set that are not present
in the second set, doing the equivalent of the MINUS set operator. The MULTISET EXCEPT
DISTINCT operator will remove any duplicates.

SET SERVEROUTPUT ON
DECLARE
TYPE t_tab IS TABLE OF NUMBER;
l_tab1 t_tab := t_tab(1,2,3,4,5,6,7,8,9,10);
 l_tab2 t_tab := t_tab(6,7,8,9,10);
BEGIN
l_tab1 := l_tab1 MULTISET EXCEPT l_tab2;

FOR i IN l_tab1.first .. l_tab1.last LOOP

DBMS_OUTPUT.put_line(l_tab1(i));
END LOOP;
END;
/
1
2
3
4
5

PL/SQL procedure successfully completed.

SQL>

The NOT keyword can be included to get the inverse. For example NOT MULTISET EXCEPT.

MULTISET INTERSECT {DISTINCT} Operator

The MULTISET INTERSECT operator returns the elements that are present in both sets,
doing the equivalent of the INTERSECT set operator. The MULTISET INTERSECT
DISTINCT operator will remove any duplicates.

SET SERVEROUTPUT ON
DECLARE
TYPE t_tab IS TABLE OF NUMBER;
l_tab1 t_tab := t_tab(1,2,3,4,5,6,7,8,9,10);
l_tab2 t_tab := t_tab(6,7,8,9,10);
BEGIN
l_tab1 := l_tab1 MULTISET INTERSECT l_tab2;

FOR i IN l_tab1.first .. l_tab1.last LOOP

DBMS_OUTPUT.put_line(l_tab1(i));
END LOOP;
END;
/
6
7
8
9
10

PL/SQL procedure successfully completed.

SQL>

The NOT keyword can be included to get the inverse. For example NOT MULTISET

INTERSECT.

MULTISET Conditions

IS {NOT} A SET Condition

The IS {NOT} A SET condition is used to test if a collection is populated by unique
elements, or not. If the collection is not initialized the function will return NULL. An
initialised and empty collection will return true.

SET SERVEROUTPUT ON
DECLARE
TYPE t_tab IS TABLE OF NUMBER;
l_null_tab t_tab := NULL;
l_empty_tab t_tab := t_tab();
l_set_tab t_tab := t_tab(1,2,3,4);
l_not_set_tab t_tab := t_tab(1,2,3,4,4,4);

FUNCTION display (p_in BOOLEAN) RETURN VARCHAR2 AS

BEGIN
IF p_in IS NULL THEN
RETURN 'NULL';
ELSIF p_in THEN
RETURN 'TRUE';
ELSE
RETURN 'FALSE';
END IF;
END;
BEGIN
DBMS_OUTPUT.put_line('l_null_tab IS A SET = ' || display(l_null_tab IS
A SET));
DBMS_OUTPUT.put_line('l_null_tab IS NOT A SET = ' || display(l_null_tab IS
NOT A SET));
DBMS_OUTPUT.put_line('l_empty_tab IS A SET = ' || display(l_empty_tab
IS A SET));
DBMS_OUTPUT.put_line('l_empty_tab IS NOT A SET = ' || display(l_empty_tab
IS NOT A SET));
DBMS_OUTPUT.put_line('l_set_tab IS A SET = ' || display(l_set_tab IS
A SET));
DBMS_OUTPUT.put_line('l_set_tab IS NOT A SET = ' || display(l_set_tab IS
NOT A SET));
DBMS_OUTPUT.put_line('l_not_set_tab IS A SET = ' || display(l_not_set_tab
IS A SET));
 DBMS_OUTPUT.put_line('l_not_set_tab IS NOT A SET = ' || display(l_not_set_tab
IS NOT A SET));
END;
/
l_null_tab IS A SET = NULL
l_null_tab IS NOT A SET = NULL
l_empty_tab IS A SET = TRUE
l_empty_tab IS NOT A SET = FALSE
l_set_tab IS A SET = TRUE
l_set_tab IS NOT A SET = FALSE
l_not_set_tab IS A SET = FALSE
l_not_set_tab IS NOT A SET = TRUE

PL/SQL procedure successfully completed.

SQL>

IS {NOT} EMPTY Condition

The IS {NOT} EMPTY condition is used to test if a collection is empty, or not. If the
collection is not initialized the function will return NULL.

SET SERVEROUTPUT ON
DECLARE
TYPE t_tab IS TABLE OF NUMBER;
l_null_tab t_tab := NULL;
l_empty_tab t_tab := t_tab();
l_not_empty_tab t_tab := t_tab(1,2,3,4,4,4);

FUNCTION display (p_in BOOLEAN) RETURN VARCHAR2 AS

BEGIN
IF p_in IS NULL THEN
RETURN 'NULL';
ELSIF p_in THEN
RETURN 'TRUE';
ELSE
RETURN 'FALSE';
END IF;
END;
BEGIN
DBMS_OUTPUT.put_line('l_null_tab IS EMPTY = ' || display(l_null_tab IS
EMPTY));
DBMS_OUTPUT.put_line('l_null_tab IS NOT EMPTY = ' || display(l_null_tab IS
NOT EMPTY));
DBMS_OUTPUT.put_line('l_empty_tab IS EMPTY = ' || display(l_empty_tab
IS EMPTY));
DBMS_OUTPUT.put_line('l_empty_tab IS NOT EMPTY = ' || display(l_empty_tab
IS NOT EMPTY));
 DBMS_OUTPUT.put_line('l_not_empty_tab IS EMPTY = ' ||
display(l_not_empty_tab IS EMPTY));
DBMS_OUTPUT.put_line('l_not_empty_tab IS NOT EMPTY = ' ||
display(l_not_empty_tab IS NOT EMPTY));
END;
/
l_null_tab IS EMPTY = NULL
l_null_tab IS NOT EMPTY = NULL
l_empty_tab IS EMPTY = TRUE
l_empty_tab IS NOT EMPTY = FALSE
l_not_empty_tab IS EMPTY = FALSE
l_not_empty_tab IS NOT EMPTY = TRUE

PL/SQL procedure successfully completed.

SQL>

MEMBER Condition
The MEMBER condition allows you to test if an element is member of a collection.

SET SERVEROUTPUT ON
DECLARE
TYPE t_tab IS TABLE OF NUMBER;
l_tab1 t_tab := t_tab(1,2,3,4,5);
BEGIN
DBMS_OUTPUT.put('Is 3 MEMBER OF l_tab1? ');
IF 3 MEMBER OF l_tab1 THEN
DBMS_OUTPUT.put_line('TRUE');
ELSE
DBMS_OUTPUT.put_line('FALSE');
END IF;
END;
/
Is 3 MEMBER OF l_tab1? TRUE

PL/SQL procedure successfully completed.

SQL>

The NOT keyword can be included to get the inverse. For example NOT MEMBER.

The OF keyword is optional, but makes the code more readable.

SUBMULTISET Condition
The SUBMULTISET condition returns true if the first collection is a subset of the second.
SET SERVEROUTPUT ON
DECLARE
TYPE t_tab IS TABLE OF NUMBER;
l_tab1 t_tab := t_tab(1,2,3,4,5);
l_tab2 t_tab := t_tab(1,2,3);
l_tab3 t_tab := t_tab(1,2,3,7);
BEGIN
DBMS_OUTPUT.put('Is l_tab2 SUBMULTISET OF l_tab1? ');
IF l_tab2 SUBMULTISET OF l_tab1 THEN
DBMS_OUTPUT.put_line('TRUE');
ELSE
DBMS_OUTPUT.put_line('FALSE');
END IF;

DBMS_OUTPUT.put('Is l_tab3 SUBMULTISET OF l_tab1? ');

IF l_tab3 SUBMULTISET OF l_tab1 THEN
DBMS_OUTPUT.put_line('TRUE');
ELSE
DBMS_OUTPUT.put_line('FALSE');
END IF;
END;
/
Is l_tab2 SUBMULTISET OF l_tab1? TRUE
Is l_tab3 SUBMULTISET OF l_tab1? FALSE

PL/SQL procedure successfully completed.

SQL>

Having duplicate values in the main set is fine.

SET SERVEROUTPUT ON
DECLARE
TYPE t_tab IS TABLE OF NUMBER;
l_tab1 t_tab := t_tab(1,2,3,3,4,5);
l_tab2 t_tab := t_tab(1,2,3);
BEGIN
DBMS_OUTPUT.put('Is l_tab2 SUBMULTISET OF l_tab1? ');
IF l_tab2 SUBMULTISET OF l_tab1 THEN
DBMS_OUTPUT.put_line('TRUE');
ELSE
DBMS_OUTPUT.put_line('FALSE');
END IF;
END;
/
Is l_tab2 SUBMULTISET OF l_tab1? TRUE

PL/SQL procedure successfully completed.

SQL>
Having duplicate values in the subset results in false, if those duplicates are not present
in the main set.

SET SERVEROUTPUT ON
DECLARE
TYPE t_tab IS TABLE OF NUMBER;
l_tab1 t_tab := t_tab(1,2,3,4,5);
l_tab2 t_tab := t_tab(1,2,3,3);
BEGIN
DBMS_OUTPUT.put('Is l_tab2 SUBMULTISET OF l_tab1? ');
IF l_tab2 SUBMULTISET OF l_tab1 THEN
DBMS_OUTPUT.put_line('TRUE');
ELSE
DBMS_OUTPUT.put_line('FALSE');
END IF;
END;
/
Is l_tab2 SUBMULTISET OF l_tab1? FALSE

PL/SQL procedure successfully completed.

SQL>

If we add the duplicates into both the main set and the subset, it returns true.

SET SERVEROUTPUT ON
DECLARE
TYPE t_tab IS TABLE OF NUMBER;
l_tab1 t_tab := t_tab(1,2,3,4,5,3);
l_tab2 t_tab := t_tab(1,2,3,3);
BEGIN
DBMS_OUTPUT.put('Is l_tab2 SUBMULTISET OF l_tab1? ');
IF l_tab2 SUBMULTISET OF l_tab1 THEN
DBMS_OUTPUT.put_line('TRUE');
ELSE
DBMS_OUTPUT.put_line('FALSE');
END IF;
END;
/
Is l_tab2 SUBMULTISET OF l_tab1? TRUE

PL/SQL procedure successfully completed.

SQL>

An initialised, but empty subset will always return true.

SET SERVEROUTPUT ON
DECLARE
TYPE t_tab IS TABLE OF NUMBER;
l_tab1 t_tab := t_tab(1,2,3,4,5);
l_tab2 t_tab := t_tab();
BEGIN
DBMS_OUTPUT.put('Is l_tab2 SUBMULTISET OF l_tab1? ');
IF l_tab2 SUBMULTISET OF l_tab1 THEN
DBMS_OUTPUT.put_line('TRUE');
ELSE
DBMS_OUTPUT.put_line('FALSE');
END IF;
END;
/
Is l_tab2 SUBMULTISET OF l_tab1? TRUE

PL/SQL procedure successfully completed.

SQL>

The result is also true if both sets are empty.

SET SERVEROUTPUT ON
DECLARE
TYPE t_tab IS TABLE OF NUMBER;
l_tab1 t_tab := t_tab();
l_tab2 t_tab := t_tab();
BEGIN
DBMS_OUTPUT.put('Is l_tab2 SUBMULTISET OF l_tab1? ');
IF l_tab2 SUBMULTISET OF l_tab1 THEN
DBMS_OUTPUT.put_line('TRUE');
ELSE
DBMS_OUTPUT.put_line('FALSE');
END IF;
END;
/
Is l_tab2 SUBMULTISET OF l_tab1? TRUE

PL/SQL procedure successfully completed.

SQL>

The NOT keyword can be included to get the inverse. For example NOT SUBMULTISET.

The OF keyword is optional, but makes the code more readable.

MULTISET Functions

CARDINALITY Function
The CARDINALITY function returns the number of elements in the collection, similar to
the COUNT method, but it is available from SQL.

CREATE OR REPLACE TYPE t_number_tab AS TABLE OF NUMBER(10);

/

SELECT CARDINALITY(tab1)
FROM (SELECT t_number_tab (1, 2, 3, 4) AS tab1
FROM dual);

CARDINALITY(TAB1)
-----------------
4

SQL>

SELECT tab1
FROM (SELECT t_number_tab(1, 2, 3, 4) AS tab1
FROM dual)
WHERE CARDINALITY(tab1) = 4;

TAB1
--------------------------------------------------------------------------------
T_NUMBER_TAB(1, 2, 3, 4)

SQL>

From PL/SQL you can use with the COUNT method or the CARDINALITY function.

SET SERVEROUTPUT ON
DECLARE
TYPE t_tab IS TABLE OF NUMBER;
l_tab1 t_tab := t_tab(1,2,3,4,5);
BEGIN
DBMS_OUTPUT.put_line('COUNT = ' || l_tab1.COUNT);
DBMS_OUTPUT.put_line('CARDINALITY = ' || CARDINALITY(l_tab1));
END;
/
COUNT = 5
CARDINALITY = 5

PL/SQL procedure successfully completed.

SQL>

POWERMULTISET Function
The POWERMULTISET function accepts a nested table and returns a "nested table of nested
tables" containing all the possible subsets from the original nested table.

CREATE OR REPLACE TYPE t_number_tab AS TABLE OF NUMBER(10);

/

SELECT *
FROM TABLE(POWERMULTISET(t_number_tab (1, 2, 3, 4)));

COLUMN_VALUE
----------------------------------------
T_NUMBER_TAB(1)
T_NUMBER_TAB(2)
T_NUMBER_TAB(1, 2)
T_NUMBER_TAB(3)
T_NUMBER_TAB(1, 3)
T_NUMBER_TAB(2, 3)
T_NUMBER_TAB(1, 2, 3)
T_NUMBER_TAB(4)
T_NUMBER_TAB(1, 4)
T_NUMBER_TAB(2, 4)
T_NUMBER_TAB(1, 2, 4)
T_NUMBER_TAB(3, 4)
T_NUMBER_TAB(1, 3, 4)
T_NUMBER_TAB(2, 3, 4)
T_NUMBER_TAB(1, 2, 3, 4)

SQL>

POWERMULTISET_BY_CARDINALITY Function
The POWERMULTISET_BY_CARDINALITY function is similar to the POWERMULTISET function, but
it allows us to limit the output to just those subsets that have the specified cardinality. In
the following example we return only those subsets that have a cardinality of 2.

SELECT *
FROM TABLE(POWERMULTISET_BY_CARDINALITY(t_number_tab (1, 2, 3, 4), 2));

COLUMN_VALUE
----------------------------------------
T_NUMBER_TAB(1, 2)
T_NUMBER_TAB(1, 3)
T_NUMBER_TAB(1, 4)
T_NUMBER_TAB(2, 3)
T_NUMBER_TAB(2, 4)
T_NUMBER_TAB(3, 4)

SQL>
SET Function
The SET function returns a collection containing the distinct values from a collection.

CREATE OR REPLACE TYPE t_number_tab AS TABLE OF NUMBER(10);

/

SET LINESIZE 100

COLUMN basic_out FORMAT A35
COLUMN set_out FORMAT A35

SELECT tab1 AS basic_out,

SET(tab1) AS set_out,
CARDINALITY(tab1) AS card_out,
CARDINALITY(SET(tab1)) AS card_set
FROM (SELECT t_number_tab (1, 2, 3, 4, 4, 4) AS tab1
FROM dual);

BASIC_OUT SET_OUT
CARD_OUT CARD_SET
----------------------------------- -----------------------------------
---------- ----------
T_NUMBER_TAB(1, 2, 3, 4, 4, 4) T_NUMBER_TAB(1, 2, 3, 4)
6 4

SQL>

This is available from PL/SQL also.

SET SERVEROUTPUT ON
DECLARE
TYPE t_tab IS TABLE OF NUMBER;
l_tab1 t_tab := t_tab(1,2,3,4,4,4);
BEGIN
DBMS_OUTPUT.put_line('CARDINALITY = ' || CARDINALITY(l_tab1));
DBMS_OUTPUT.put_line('CARDINALITY SET = ' || CARDINALITY(SET(l_tab1)));
END;
/
CARDINALITY = 6
CARDINALITY SET = 4

PL/SQL procedure successfully completed.

SQL>

Multidimensional Collections
In addition to regular data types, collections can be based on record types, allowing the
creation of two-dimensional collections.

SET SERVEROUTPUT ON

-- Collection of records.
DECLARE
TYPE t_row IS RECORD (
id NUMBER,
description VARCHAR2(50)
);

TYPE t_tab IS TABLE OF t_row;

l_tab t_tab := t_tab();
BEGIN
FOR i IN 1 .. 10 LOOP
l_tab.extend();
l_tab(l_tab.last).id := i;
l_tab(l_tab.last).description := 'Description for ' || i;
END LOOP;
END;
/

-- Collection of records based on ROWTYPE.

CREATE TABLE t1 (
id NUMBER,
description VARCHAR2(50)
);

SET SERVEROUTPUT ON
DECLARE
TYPE t_tab IS TABLE OF t1%ROWTYPE;
l_tab t_tab := t_tab();
BEGIN
FOR i IN 1 .. 10 LOOP
l_tab.extend();
l_tab(l_tab.last).id := i;
l_tab(l_tab.last).description := 'Description for ' || i;
END LOOP;
END;
/

For multidimentional arrays you can build collections of collections.

DECLARE
TYPE t_tab1 IS TABLE OF NUMBER;
TYPE t_tab2 IS TABLE OF t_tab1;
l_tab1 t_tab1 := t_tab1(1,2,3,4,5);
l_tab2 t_tab2 := t_tab2();
BEGIN
FOR i IN 1 .. 10 LOOP
l_tab2.extend();
l_tab2(l_tab2.last) := l_tab1;
END LOOP;
END;
/