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;
I posted question to Oracle PL/SQL Community and I got clear answer from Solomon Yakobson:
(
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
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.
