ORA-06502: NULL index table key value and plsql_optimize_level

In this Blog, we will show that the logically equivalent Plsql code can have different behaviour when toggling plsql_optimize_level.

Note: Tested in Oracle 19.27

1. plsql_optimize_level: 0 or 1

If we run all 4 tests below in Plsql code blocks with 0 or 1, they all throw the same error: ORA-06502: NULL index table key value.

alter session set plsql_optimize_level = 1;

--------- Test-1 ---------
declare
  type t is table of number index by binary_integer;
  v_t  t;
  i    pls_integer := null;
begin
  v_t(1) := 1;
  v_t(null) := v_t(1);
  dbms_output.put_line(v_t(null));
end;
/

--------- Test-2: error: NULL index table key value ---------
declare
  type t is table of number index by binary_integer;
  v_t  t;
  i    pls_integer := null;
begin
  v_t(1) := 1;
  v_t(i) := v_t(1);
  dbms_output.put_line(v_t(i));
end;
/

--------- Test-3 ---------
declare
  type t is table of number index by binary_integer;
  v_t  t;
  i    pls_integer := null;
begin
  if true then
    dbms_output.put_line(1);
  end if;
  v_t(1) := 1;
  v_t(null) := v_t(1);
  dbms_output.put_line(v_t(null));
end;
/

--------- Test-4: error: NULL index table key value ---------
declare
  type t is table of number index by binary_integer;
  v_t  t;
  i    pls_integer := null;
begin
  if v_t.exists(1) or not v_t.exists(1) then
    dbms_output.put_line(1);
  end if;
  v_t(1) := 1;
  v_t(null) := v_t(1);
  dbms_output.put_line(v_t(null));
end;
/

2. plsql_optimize_level: 2 or 3

When we run above 4 Plsql code blocks with plsql_optimize_level: 2 (Default value) or 3:

  alter session set plsql_optimize_level = 2;

we can see that Test-1 and Test-3 are OK, but Test-2 and Test-4 hit:

   ORA-06502: PL/SQL: numeric or value error: NULL index table key value

although Test-1 and Test-2 are logically equivalent, as well as Test-3 and Test-4.

3. PLSQ/SQL MCODE and DIANA Nodes

We tried to use PL/SQL event 10928, 10938 and dumpdian.sql to dump runtime PLSQ/SQL MCODE and DIANA Nodes:

event 10928 PL/SQL Machine Code Trace 
event 10938 PL/SQL calls and exceptions
admin/dumpdian.sql

(see:
   PL/SQL Machine Code Trace - event 10928, 
   PL/SQL, AST, DIANA, Attributes and IDL
)

but it is still hard to observe any difference.

alter session set events '10928 trace name context forever, level 1';
--------- Test-1 ---------
declare
  type t is table of number index by binary_integer;
  v_t  t;
  i    pls_integer := null;
begin
  v_t(1) := 1;
  v_t(null) := v_t(1);
  dbms_output.put_line(v_t(null));
end;
/
alter session set events '10928 trace name context off';


--  Run under sys 
create or replace procedure test_1_proc as
  type t is table of number index by binary_integer;
  v_t  t;
  i    pls_integer := null;
begin
  v_t(1) := 1;
  v_t(null) := v_t(1);
  dbms_output.put_line(v_t(null));
end;
/

-- dump into session trc file --

exec sys.dumpdiana.dump(aname => 'TEST_1_PROC');
exec sys.dumpdiana.node_count(aname => 'TEST_1_PROC');

We have also tried to switch plsql_code_type from interpreted (Default ) to native, no effect is observed.