Collections in Oracle - Part 2

Collections continued .....

Object Oriented Programing concepts
  -- Define a class  ( Type xxxx of <collection type> of <data type>)
  -- Instantiate an object of the class ( Variable_X  <the above type>)
  -- Use the methods on the created object (use collection methods on Variable_X collection)

Collections are oracle's predefined Types(classes) & to manipulate we are provided with a set of methods.

Methods supplied by Oracle:

1.Count : Returns the count of elements in a collection.

2.Exists : Returns True if the element exists.

3.First : Returns the subscript of the first element in the collection.

4.Last : Returns the subscript of the last element in the collection.

5.Prior : Traverse to Previous element in the collection.

6.Next : Traverse to next element in the collection.

7.Extend : Create an empty place holder & ready for accepting a value.

8.Delete : Clean up the value in the specified subscript but the place holder exists.

9.Trim : Cleans up the value in the specified subscript and removes the place holder.(need to use extend, if needed to add the trimmed subscript). Trim will be done from back of the collection.



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';
           happyfamily.extend(4);
happyfamily (7) := 'x1';
happyfamily (8) := 'x2';
happyfamily (9) := 'x3';
happyfamily (10) := 'x4';
           FOR INDX IN happyfamily.FIRST .. happyfamily.LAST
           LOOP
             DBMS_OUTPUT.put_line (happyfamily (INDX));
          END LOOP;
dbms_output.put_line('Count of elements:'||happyfamily.count);
dbms_output.put_line('Subscript of first element:'|| happyfamily.first);
dbms_output.put_line('Delete element at subscript 7');
happyfamily.delete(7);
dbms_output.put_line('Count of elements:'||happyfamily.count);
dbms_output.put_line('Delete element at subscript 1');
happyfamily.delete(1);
dbms_output.put_line('Count of elements:'||happyfamily.count);
dbms_output.put_line('Subscript of first element:'|| happyfamily.first);
dbms_output.put_line('ADD ELEMENT AT SUBSCRIPT 1');
happyfamily(1):='KIRAN';
dbms_output.put_line('Subscript of first element:'|| happyfamily.first);
dbms_output.put_line('Subscript of LAST element:'|| happyfamily.last);
dbms_output.put_line('Trim the last element');
happyfamily.trim;
dbms_output.put_line('Count of elements:'||happyfamily.count);
dbms_output.put_line('Subscript of LAST element:'|| happyfamily.last);
dbms_output.put_line('Check Exists at subscript 7');
if happyfamily.exists(7) then
dbms_output.put_line ('element exists :' || happyfamily(7));
else
dbms_output.put_line('element missing');
end if;
dbms_output.put_line('Check Exists at subscript 10 : which was trimmed');
if happyfamily.exists(10) then
dbms_output.put_line ('element exists :' || happyfamily(10));
else
dbms_output.put_line('element missing');
end if;
dbms_output.put_line ('Add new value to subscript 10: which was trimmed');
happyfamily(10):='dummy';
dbms_output.put_line ('Value at subscript 10'||happyfamily(10));
exception
when others then
dbms_output.put_line(substr(sqlerrm,1,100));
dbms_output.put_line('Extend the collection to add new element which was previously trimmed');
happyfamily.extend;
happyfamily(10):='dummy';
dbms_output.put_line ('Value at subscript 10:'||happyfamily(10));
END;
/

Exceptions possible:

If COUNT is applied to an uninitialized nested table or a VARRAY, it raises the COLLECTION_IS_NULL predefined exception

If DELETE is applied to an uninitialized nested table or a VARRAY, it raises the COLLECTION_IS_NULL predefined exception.

If EXISTS is applied to an uninitialized  nested table or a VARRAY, or an initialized collection with no elements, it simply returns FALSE. You can use EXISTS beyond the COUNT without raising an exception.

If EXTEND is applied to an uninitialized nested table or a VARRAY, it raises the COLLECTION_IS_NULL predefined exception. 

An attempt to EXTEND a VARRAY beyond its declared limit raises the SUBSCRIPT_BEYOND_LIMIT exception.

If FIRST and LAST are applied to an uninitialized nested table or a VARRAY, they raise the COLLECTION_ IS_NULL predefined exception.

If LIMIT is applied to an uninitialized nested table or a VARRAY, it raises the COLLECTION_ IS_NULL predefined exception.

If PRIOR and NEXT are applied to an uninitialized nested table or a VARRAY, they raise the COLLECTION_ IS_NULL predefined exception.

The TRIM method will raise the SUBSCRIPT_BEYOND_COUNT predefined exception if you attempt to TRIM more elements than actually exist.
If TRIM is applied to an uninitialized nested table or a VARRAY, it raises the COLLECTION_IS_NULL predefined exception