Oracle SQL and PL/SQL context switch as reason for unexpected ORA-06533: Subscript beyond count

 If I run this script, I get ORA-06553 :

CREATE TABLE TBL
(
  COL  NUMBER
);

declare
ln_numbers   apex_t_number := apex_t_number(1,2);
ln_numbers_count NUMBER := ln_numbers.count;
begin
for i in 1..ln_numbers_count + 1 loop
  dbms_output.put_line('i: '||i||' ln_numbers_count: '||ln_numbers_count||
  ' inserting '||case when i <= ln_numbers_count then ln_numbers(i) else 3 end);
  insert into TBL(COL) values(case when i <= ln_numbers_count then ln_numbers(i) else 3 end);
  dbms_output.put_line('insert completed');
end loop;
EXCEPTION
  WHEN OTHERS THEN
  dbms_output.put_line(SQLERRM);
  raise;
end;

DBMS OUTPUT:

i: 1 ln_numbers_count: 2 inserting 1

insert completed

i: 2 ln_numbers_count: 2 inserting 2

insert completed

i: 3 ln_numbers_count: 2 inserting 3

ORA-06533: Subscript beyond count

It seems like Oracle tries to claculate ln_numbers(i) for i=3 but I can not figure out why? Case sentence is

case when i <= ln_numbers_count then ln_numbers(i) else 3 end

and ln_number_count is 2. It should work without error. 

If I extract case sentence outside insert it works as expected:

declare

ln_numbers   apex_t_number := apex_t_number(1,2);

ln_numbers_count NUMBER := ln_numbers.count;

ln_number NUMBER;

begin

for i in 1..ln_numbers_count + 1 loop

  ln_number := case when i <= ln_numbers_count then ln_numbers(i) else 3 end;

  dbms_output.put_line('i: '||i||' ln_numbers_count: '||ln_numbers_count||

  ' inserting '||ln_number);   

  insert into TBL(COL) values(ln_number);

  dbms_output.put_line('insert completed');

end loop;

EXCEPTION

  WHEN OTHERS THEN

  dbms_output.put_line(SQLERRM);

  raise;

end;

DBMS OUTPUT:

i: 1 ln_numbers_count: 2 inserting 1

insert completed

i: 2 ln_numbers_count: 2 inserting 2

insert completed

i: 3 ln_numbers_count: 2 inserting 3

insert completed

I posted question to Oracle PL/SQL Community and I got clear answer from Solomon Yakobson:

When you execute

ln_number := case when i <= ln_numbers_count then ln_numbers(i) else 3 end;

case statement is part of PL/SQL and short circuit evaluation is applied by PL/SQL and ln_numbers(3) is never evluated. 

However when you execute:

insert into TBL(COL) values(case when i <= ln_numbers_count then ln_numbers(i) else 3 end);

case statement is not part of PL/SQL code, it is part of SQL code (insert statement). Therefore insert transforms into

INSERT INTO TBL(COL) VALUES(CASE WHEN :B3 <= :B2 THEN :B1 ELSE 3 END)

where bind variable :B3 is assigned PL/SQL variable i value, bind variable :B2 is assigned PL/SQL variable ln_numbers_count value and :B1 is assigned PL/SQL variable ln_numbers(i) value. As you probaly see it now, case statement becomes part of SQL and third iteration is trying to bind :B1 with  ln_numbers(3) before SQL executes case statement.

How to check equality of two SQL select result sets in Oracle

Often you need to know if the new SQL select statement is equivalent to old one. Let’s say You rewrite select and it’s much faster, but is it equivalent to old one? You need to compare result sets of old and new select statements. You can compare result sets with different combinations of bind variables. If you don’t have any bind variables, create them by replacing constant values. If results sets are equal in all test scenarios you are good to go. Probability that new SQL select statement is equivalent to old one is greater, if you test selects with more different combinations of bind variables and if result sets are bigger.

Select for comparing old select with new select is:

WITH old_select as (select …)

,new_select as (select …)

select count(*), ‘diffs’ description from

 (

         (

                select * from old_select

                        minus

                 select * from new_select

             )

             union

             (

                    select * from new_select

                        minus

                    select * from old_select

                )

  )

  union all

  select count(*), 'new_select rows' description from new_select   

  union all

  select count(*), 'old_select rows' description from old_select   

 ;

Following result set means that result sets are equal and that there are 130 rows in each result set.

0      ‘diffs’

130    'new_select rows'

130    'old_select rows'