Oracle Collections (Index-By table)

A collection is an ordered group of elements, all of the same type. It is a general concept that encompasses lists, arrays, and other familiar datatypes. Each element has a unique subscript that determines its position in the collection.

types of collections

• Index-by table (associative arrays)
• Nested tables
• Varrays ( variable size arrays)

If you already have code or business logic that uses some other language, you can usually translate that language's array and set types directly to PL/SQL collection types.

• Arrays in other languages become VARRAYs in PL/SQL.
• Sets and bags in other languages become nested tables in PL/SQL.
• Hash tables and other kinds of unordered lookup tables in other languages become associative arrays in PL/SQL.

Index-by table

DECLARE
    i            VARCHAR (50);
    TYPE arraylike IS TABLE OF VARCHAR2 (50)
        INDEX BY VARCHAR2 (50);
    datearray    arraylike;
    cutidarray   arraylike;
BEGIN
    datearray (1) := '02-08-2015';
    datearray (2) := '02-08-2015';
    datearray (3) := '06-08-2015';
    datearray (4) := '06-08-2015';
    datearray (5) := '04-08-2015';
    datearray (6) := '04-08-2015';
    datearray (7) := '02-08-2015';
    datearray (8) := '04-08-2015';
    datearray (9) := '06-08-2015';
    datearray (10) := '02-08-2015';
    datearray (11) := '06-08-2015';
    datearray (12) := '04-08-2015';
    datearray (13) := '02-08-2015';
    datearray (14) := '06-08-2015';
    datearray (15) := '02-08-2015';
    cutidarray (1) := '11684129';
    cutidarray (2) := '11192994';
    cutidarray (3) := '11300838';
    cutidarray (4) := '11281815';
    cutidarray (5) := '11228291';
    cutidarray (6) := '11129267';
    cutidarray (7) := '11114680';
    cutidarray (8) := '11183177';
    cutidarray (9) := '11242009';
    cutidarray (10) := '11200434';
    cutidarray (11) := '11328917';
    cutidarray (12) := '11130167';
    cutidarray (13) := '11172261';
    cutidarray (14) := '11401292';
    cutidarray (15) := '11893237';

    FOR i IN 1 .. 15
    LOOP
        UPDATE m01_cma_complient_details cma
           SET cma.is_secondery_update = 1,
               cma.m01_other_next_review =
                   TO_DATE (datearray (i), 'dd-mm-yyyy')
         WHERE cma.m01_customer_id IN
                   (SELECT m01.m01_customer_id
                      FROM m01_customer m01
                     WHERE m01.m01_c1_customer_id = cutidarray (i));
    /*DBMS_OUTPUT.put_line (
        datearray (i) || '-----------' || cutidarray (i)); */
    END LOOP;
END;