Oracle 11g : When declaring new TYPE as TABLE, must I add "INDEX BY PLS_INTEGER"?

Question

What is the diffecence between adding INDEX BY PLS_INTEGER and not at end of declaration of new table type. Look at this example:

DECLARE
    GC_BULK_LIMIT CONSTANT INTEGER := 500;
    CURSOR CUR_CLIENTS IS SELECT C.ID, C.NAME FROM CLIENTS C;
    TYPE RT_CLIENTS IS TABLE OF CUR_CLIENTS%ROWTYPE;
    -- TYPE RT_CLIENTS IS TABLE OF CUR_CLIENTS%ROWTYPE INDEX BY PLS_INTEGER;
    LT_CLIENTS RT_CLIENTS;
BEGIN
    OPEN CUR_CLIENTS;
    LOOP
        FETCH CUR_CLIENTS BULK COLLECT INTO LT_CLIENTS LIMIT GC_BULK_LIMIT;
        EXIT WHEN LT_CLIENTS.COUNT = 0;
        FOR I IN 1..LT_CLIENTS.COUNT LOOP
            -- ... SOME LOGIC
        END LOOP;
    END LOOP;
    CLOSE CUR_CLIENTS;
END;

Answer 1

in response to "must i add". The short answer is NO.

the difference is that

TYPE RT_CLIENTS IS TABLE OF CUR_CLIENTS%ROWTYPE;

Is a nested table. This means that for a given variable of this type, we know that the subscripts are sequential. i.e. the subscript starts from 1 and goes up to the array length.

The following loop therefore, is the right way to access a nested table array:

FOR I IN 1..LT_CLIENTS.COUNT LOOP

This however, is called a associative array:

TYPE RT_CLIENTS IS TABLE OF CUR_CLIENTS%ROWTYPE INDEX BY PLS_INTEGER;

(you could also index by a varchar2 if you wanted). The difference is that the subscripts in this case do not have to be sequential, depending on how the array was populated. In your code, they would be (as bulk collect would do that), but its not always the case.

The safe way to access and loop through an index by array is :

  v_subscript := t_arr.first;
  while v_subscript is not null loop
    dbms_output.put_line(v_subscript || ': ' || t_arr(v_subscript));
    v_subscript := t_arr.next(v_subscript); 
  end loop;

where v_subscript is a variable of the same datatype of the index by part.

also with a nested table, you can populate the array quickly with:

declare
  type myarr is table of number;
  t_arr         myarr;
    v_subscript   number;
begin
  t_arr := myarr(1, 12, 44);

whereas with an index by array you'd have to have three lines there to populate it:

  t_arr(1):= 1;
  t_arr(2):= 12;
  t_arr(3):= 44;

for your particular case, without the index by is perfectly fine.

further reading: http://docs.oracle.com/cd/E18283_01/appdev.112/e17126/composites.htm