This has me confused. Usually, an INDEX BY VARCHAR2 associative array is not allowed to have a null index. But sometimes it does? Is there something here I'm not understanding right, or is this a bug?
declare
type tp_strarray is table of pls_integer index by varchar2(32767);
str_array tp_strarray;
s varchar2(32767);
begin
s := 'foo';
str_array( s ) := 1;
-- str_array( null ) := 2;
-- ORA-06502: PL/SQL: numeric or value error: NULL index table key value
-- str_array( nvl( null, '' )) := 3;
-- ORA-06502: PL/SQL: numeric or value error: NULL index table key value
str_array( 'bar' ) := 4;
s := null;
str_array( nvl( s, '' )) := 5;
dbms_output.put_line( 'count=' || str_array.count );
-- count=3
end;
Using a literal null or nvl(null, '') as the index raises an exception. I assume the latter case is simplified by the optimizer to a literal null. But it works when I use an intermediate variable. I get the feeling it's not supposed to?
I've found this on my 12.2.0.1 instance, on 12.1.x.x on apex.oracle.com, and on 18.0 on Live SQL.