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. Can please someone describe what happens here?
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