INDICES OF Clause In Oracle10g

The INDICES OF clause allows us to load non-contiguous (sparse) arrays by telling Oracle to use just the elements that are populated. Remember in versions prior to 10g that arrays had to be dense and we would use iterators such as [array.FIRST .. array.LAST] or [1 .. array.COUNT] to address them. Check below example

DECLARE
   TYPE my_neted_tbl IS TABLE OF NUMBER;

   var_nt   my_neted_tbl
                        := my_neted_tbl (6, 8, 4, 11, 23, 45, 67, 55, 43, 22);
          /*this we call as dense collection because every index having value*/
BEGIN
   var_nt.DELETE (3, 6);
   /*After deleting data it became a sparse collection*/
   FORALL idx IN INDICES OF var_nt
      INSERT INTO tbl_name
                  (col_name
                  )
           VALUES (var_nt (idx)
                  );
END;

1. Created one nested table
2. In this nested table i am creating 10 index by assigning 10 numeric element.
3. Now i am deleting data for index 3 to 6 value (var_nt.delete(3,6);)  --after deleiting values it became sparse collection
4. By using  FORALL we are inserting data into table , if we are inserting sparse collection then we need to use INDICES OF otherwise it will through error please check below example

Now i am using INDICES OF clause:

Steps1:  Create this table to insert data by using FORALL

CREATE TABLE INDICES_OF_TBL ( id INT, val VARCHAR2(128) );

Steps2:  Create this subprogram and run it.

DECLARE

      TYPE aat_rec IS TABLE OF INDICES_OF_TBL %ROWTYPE
         INDEX BY PLS_INTEGER;
      aa_recs aat_rec;

   BEGIN

      /* Load a sparse array... */
      SELECT object_id, object_name BULK COLLECT INTO aa_recs
      FROM   all_objects
      WHERE  ROWNUM <= 10;

      aa_recs.DELETE(2);
      aa_recs.DELETE(4);
      aa_recs.DELETE(6);

      /* Load table using sparse array... */
      FORALL i IN INDICES OF aa_recs
         INSERT INTO INDICES_OF_TBL 
         VALUES aa_recs(i);

      DBMS_OUTPUT.PUT_LINE(
         TO_CHAR(SQL%ROWCOUNT) || ' rows inserted.'
         );

   END;
   /