VARRAY

VARRAY stands for variable-size array.

It is an array that can be either manipulated as a whole or individually as elements.

It has a maximum size and can contain 0 to any number of elements up to the maximum specified.

VARRAY is stored in-line.

That means the data of a column of VARRAY type is stored along with the remaining data of the row.

You can use VARRAY data type for:

A column in a relational table
A PL/SQL variable
A PL/SQL parameter of procedure or function
A PL/SQL function return type
A data attribute of an object type

Varray is an user defined data type, which is used to store same datatype in a single unit, it is also same as nested table i.e., before we are storing values.We must initialize the values through constructor, and also using varrays we are storing up to 2 GB data.

Varrays also supports all collection methods as same a nested table and also supports limit collection method.

This collection method returns size of the array. This is a user defined type, so we are creating in two step process.

Syntax: 

TYPE TYPENAME IS VARRAY(MAXSIZE) OF DATA TYPE(SIZE);
                                 VARIABLENAME TYPENAME:=TYPENAME();


DECLARE
   TYPE t1 IS VARRAY (10) OF VARCHAR2 (10);

   V_t   t1:= t1 ('a',
                  'b',
                  'c',
                  'd');
   Z     BOOLEAN;
BEGIN
   DBMS_OUTPUT.put_line (v_t.LIMIT);
   DBMS_OUTPUT.put_line (v_t.COUNT);
   DBMS_OUTPUT.put_line (v_t.FIRST);
   DBMS_OUTPUT.put_line (v_t.LAST);
   DBMS_OUTPUT.put_line (v_t.PRIOR (3));
   DBMS_OUTPUT.put_line (v_t.NEXT (3));
   DBMS_OUTPUT.put_line (v_t.LAST);

   FOR i IN v_t.FIRST .. v_t.LAST
   LOOP
      DBMS_OUTPUT.put_line (v_t (i));
   END LOOP;

   z := v_t.EXISTS (3);

   IF z = TRUE
   THEN
      DBMS_OUTPUT.
       put_line ('inde z exists with an element' || ' ' || v_t (3));
   ELSE
      DBMS_OUTPUT.put_line ('index z does not exists');
   END IF;

   v_t.EXTEND;
   DBMS_OUTPUT.put_line (v_t.COUNT);
   v_t (5) := 'e';
   v_t.EXTEND (2);
   v_t (6) := 'f';
   v_t (7) := 'g';

   FOR i IN v_t.FIRST .. v_t.LAST
   LOOP
      DBMS_OUTPUT.put_line (v_t (i));
   END LOOP;

   v_t.EXTEND (3, 4);

   FOR i IN v_t.FIRST .. v_t.LAST
   LOOP
      DBMS_OUTPUT.put_line (v_t (i));
   END LOOP;

   v_t.TRIM (4);
   DBMS_OUTPUT.put_line (v_t.COUNT);
   v_t.delete;
   DBMS_OUTPUT.put_line (v_t.COUNT);
END;

Note:- in varrays we are not allowed to delete range of elements, but we can delete all the elements using delete collection method