What is wrong with the WHILE Loop on PLSQL?

Question

I'm practicing collections on PL/SQL, and now I've code a simple WHILE Loop to print on screen the values of the sparse associative array. I received the following error: Oracle Error -06502: PL/SQL: numeric or value error: NULL index table key value. The results were printed on screen but oracle error as well. Thanks.

SET SERVEROUTPUT ON

DECLARE

  TYPE type_football_players IS TABLE OF VARCHAR2(45)
       INDEX BY PLS_INTEGER;

  v_costademarfil_2006 type_football_players;

  v_counter PLS_INTEGER;
BEGIN

  v_costademarfil_2006(9)  := 'Kone';
  v_costademarfil_2006(10) := 'Yapi Yapo';
  v_costademarfil_2006(11) := 'Drogba';
  v_costademarfil_2006(14) := 'Kone';
  v_costademarfil_2006(15) := 'Dindane';
  v_costademarfil_2006(19) := 'Toure';
  v_costademarfil_2006(21) := 'Eboue';

  v_counter := v_costademarfil_2006.FIRST;

  WHILE v_costademarfil_2006(v_counter) IS NOT NULL
  LOOP
        DBMS_OUTPUT.PUT_LINE(v_costademarfil_2006(v_counter));

        v_counter := v_costademarfil_2006.NEXT(v_counter);
  END LOOP;
END;

Informe de error - ORA-06502: PL/SQL: numeric or value error: NULL index table key value ORA-06512: at line 48 06502. 00000 - "PL/SQL: numeric or value error%s" *Cause: An arithmetic, numeric, string, conversion, or constraint error occurred. For example, this error occurs if an attempt is made to assign the value NULL to a variable declared NOT NULL, or if an attempt is made to assign an integer larger than 99 to a variable declared NUMBER(2). *Action: Change the data, how it is manipulated, or how it is declared so that values do not violate constraints.

Kone Yapi Yapo Drogba Kone Dindane Toure Eboue

Answer 1

Your WHILE loop condition is incorrect.

You need to check if v_counter is NOT NULL, not if v_costademarfil_2006(v_counter) is NOT NULL.

The final time around the loop, v_counter is NULL and so v_costademarfil_2006( NULL ) is evaluated. This leads to the error after printing all the names.