Empty String as Index Value in Collection (7987)

The 10 October 2011 quiz tested your knowledge of the effect of a NULL value being passed to the EXISTS method. Iudith Mentzel did some further analysis and offers up these insights:

First, as probably expected, we cannot use NULL as an array index value, neither for an associative array indexed by PLS_INTEGER nor for one indexed by a VARCHAR2, they both raise VALUE_ERROR:

DECLARE
    my_list   DBMS_SQL.number_table;
BEGIN
    my_list(NULL) := 100;

    IF my_list.EXISTS(NULL) THEN
       DBMS_OUTPUT.PUT_LINE('NULL index exists !');
    ELSE
       DBMS_OUTPUT.PUT_LINE('NULL index does not exist !');
    END IF;
END;
/
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: NULL index table key value
ORA-06512: at line 4
 
DECLARE
    TYPE array_t IS TABLE OF NUMBER INDEX BY VARCHAR2(5);
    my_list   array_t;
BEGIN
    my_list(NULL) := 100;

    IF my_list.EXISTS(NULL) THEN
       DBMS_OUTPUT.PUT_LINE('NULL index exists !');
    ELSE
       DBMS_OUTPUT.PUT_LINE('NULL index does not exist !');
    END IF;
END;
/
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: NULL index table key value
ORA-06512: at line 5

However, for a VARCHAR2 index we can use an empty string ( '' ) as an index without error, and it is NOT the same as a NULL index:
 
DECLARE
    TYPE array_t IS TABLE OF NUMBER INDEX BY VARCHAR2(5);
    my_list   array_t;
BEGIN
    my_list('') := 100;

    IF my_list.EXISTS(NULL) THEN
       DBMS_OUTPUT.PUT_LINE('NULL index exists !');
    ELSE
       DBMS_OUTPUT.PUT_LINE('NULL index does not exist !');
    END IF;

    IF my_list.EXISTS('') THEN
       DBMS_OUTPUT.PUT_LINE(' '''' index exists !');
    ELSE
       DBMS_OUTPUT.PUT_LINE(' '''' index does not exist !');
    END IF;
END;
/

NULL index does not exist !
'' index exists !

This is in spite of the fact that a VARCHAR2 variable having an empty string assigned to it is considered as NULL (which, as we know, is NOT the case for a CHAR variable):

DECLARE
     my_var   VARCHAR2(5) := '' ;
BEGIN
     IF my_var IS NULL THEN
        DBMS_OUTPUT.PUT_LINE('my_var IS NULL');
     ELSE
        DBMS_OUTPUT.PUT_LINE('my_var IS NOT NULL');
     END IF;
END;
/
my_var IS NULL

Strange NULL oddities ... 

Without this quiz, no one has probably ever thought of trying to use a NULL or a NULL variable as an array index, just to see what happens ...it is entirely the merit of PL/SQL Challenge to make us dig that deeply :-)