Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Associative array with NULL index?

Eric Olson 1Sep 19 2018 — edited Sep 21 2018

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.

This post has been answered by unknown-7404 on Sep 19 2018
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 19 2018
Added on Sep 19 2018
15 comments
2,664 views