Collections in Oracle - Part 1

Collections can be treated as Single dimensional arrays capable of holding homogeneous elements.
As in any programming, arrays will be indexed by the subscript.

Dense collection:
If the subscript of the collection is sequential with no gaps in between, we can say the collection is dense.

Sparse collection:
If the subscript of the collection has gaps in between, we can say the collection is spare.

In dense collections we can traverse using the loop counter but in case of sparse we can't use the loop counter.
So, in case of sparse collections types we use the predefined methods supplied by the oracle to traverse the collection.

Rule of Thumb:
1. Create a Type variable of the required collection type.
    ex:   Type list_of_names_t is table of varchar2(200) index by pls_integer;
2. Create an Instance of the Type ( if required Initialize with the constructor method)
    ex:   happyfamily list_of_names_t;
3. Use the Instance variable in the execution block as an array.


Oracle supports 3 types of collections(Arrays)
1. Associative Arrays
2. Nested Tables
3. Varrays


1.Associative Arrays:

They can exists only in pl/sql & are unbounded as well as spare type collection. we can use any subscript needed.

Example:

Declare
type list_of_names_t is table of varchar2(200) index by pls_integer;
happyfamily list_of_names_t;
l_row pls_integer;
Begin
happyfamily (1) :='kiran';
happyfamily (2) :='vishali';
happyfamily (10) := 'Ramprasad';
happyfamily (11) := 'Bhavani';
happyfamily (20) :='Pattabhi Rama Rao';
happyfamily (21) := 'uttara kumari';

l_row := happyfamily.first;
while (l_row is not null)
loop
dbms_output.put_line(happyfamily(l_row));
l_row := happyfamily.Next(l_row);
end loop;
end;
/


Note:  Associative Array is spare collection type and that's the reason we used
          l_row := happyfamily.first;
          l_row := happyfamily.Next(l_row);

If we have done like below it will throw an error.

for indx in happyfamily.first .. happyfamily.last
loop
dbms_output.put_line(happyfamily(indx));
end loop;
Error: ORA-01403: no data found
we can do as above, only if the collection is Dense type collection.

Declare
Type list_of_names_t is table of varchar2(200) index by pls_integer;
happyfamily list_of_names_t;
Begin
happyfamily (1) :='kiran';
happyfamily (2) :='vishali';
happyfamily (3) := 'Ramprasad';
happyfamily (4) := 'Bhavani';
happyfamily (5) :='Pattabhi Rama Rao';
happyfamily (6) := 'uttara kumari';

for indx in happyfamily.first .. happyfamily.last
loop
dbms_output.put_line(happyfamily(indx));
end loop;
end;
/

The above code works, because we made the collection dense ( subscripts 1 to 6 ) virtually.


2. Nested Table:

These can be created both in pl/sql and database tables.

These start as Dense collection type and transform to Sparse collection type up on deletion of elements.

These are unbounded, but need to be extended, when new elements need to be added.

These need to be initialized with the default constructor, at the time of instance creation or just before using it.
ex: happyfamily   list_of_names_t := list_of_names_t (  );



DECLARE
           TYPE list_of_names_t is table of varchar2(200);
           happyfamily   list_of_names_t := list_of_names_t (  );
BEGIN
           happyfamily.EXTEND;
           happyfamily (1) := 'KIRAN';
           happyfamily.EXTEND;
           happyfamily (2) := 'VISHALI';
           happyfamily.EXTEND(4);
           happyfamily (3) := 'PATTABHI RAMA RAO';
           happyfamily (4) := 'UTTARA KUMARI';
           happyfamily (5) := 'RAMPRASAD';
           happyfamily (6) := 'BHAVANI';
           FOR INDX IN happyfamily.FIRST .. happyfamily.LAST
           LOOP
             DBMS_OUTPUT.put_line (happyfamily (INDX));
          END LOOP;
END;
/

3. Varray :

These can be created both in pl/sql and database tables.

These are alway Dense type collections.

These are bounded, we set the max number of elements it can accommodate at the time of type creation.
ex: TYPE list_of_names_t is varray(10) of varchar2(200);
-- Can be extended to max of 10 element.

These need to be initialized with the default constructor, at the time of instance creation or just before using it.

ex: happyfamily   list_of_names_t := list_of_names_t (  );

DECLARE

           TYPE list_of_names_t is varray(10) of varchar2(200);

           happyfamily   list_of_names_t := list_of_names_t (  );

BEGIN

           happyfamily.EXTEND;

           happyfamily (1) := 'KIRAN';

           happyfamily.EXTEND;

           happyfamily (2) := 'VISHALI';

           happyfamily.EXTEND(4);

           happyfamily (3) := 'PATTABHI RAMA RAO';

           happyfamily (4) := 'UTTARA KUMARI';

           happyfamily (5) := 'RAMPRASAD';

           happyfamily (6) := 'BHAVANI';

           FOR INDX IN happyfamily.FIRST .. happyfamily.LAST

           LOOP

             DBMS_OUTPUT.put_line (happyfamily (INDX));

          END LOOP;

END;

/

Note: only 6 elements are filled and we still have space for 4 more. If we try to extend it above 10, we get

ORA-06532: Subscript outside of limit

Order of the elements will be preserved in Varrays.