ORA-06502: PL/SQL: numeric or value error: Bulk bind: Error in define

I came across an inexplicable error when bulk collecting into a PL/SQL table with the NOT NULL constraint the other day. What was confusing was that the code had been passing tests for quite some time.

In the end the only thing that had changed was that a VARCHAR2 which should have been non-null happened to be NULL for one particular row in the table.

Thanks to Connor for the simple test case, listed below.

If you know what might be the cause or reason behind this error, and why it doesn’t occur for dates, I’d be interested.

This was reproduced on Oracle 10.2.0.1.0.

SQL> declare
      type t is table of number not null index by pls_integer;
      r t;
     begin
      select case when rownum < 20 then rownum else null end
      bulk collect into r from all_Objects
      where rownum <= 20;
     end;
     /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: Bulk bind: Error in define
ORA-06512: at line 5

SQL> declare
      type t is table of varchar2(80) not null index by pls_integer;
      r t;
     begin
      select case when rownum < 20 then rownum else null end
      bulk collect into r from all_Objects
      where rownum <= 20;
     end;
     /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: Bulk bind: Error in define
ORA-06512: at line 5

SQL> declare
      type t is table of date not null index by pls_integer;
      r t;
     begin
      select case when rownum < 20 then sysdate else null end
      bulk collect into r from all_Objects
      where rownum <= 20;
     end;
     /
PL/SQL procedure successfully completed.