Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Unexpected ORA-06533: Subscript beyond count

Miro MasJul 8 2021 — edited Jul 8 2021

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
This post has been answered by mathguy on Jul 8 2021
Jump to Answer

Comments

Solomon Yakobson

This is a good one. 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 third iteration is trying to bind ln_numbers(3) before SQL executes case statement.
SY.

1 person found this helpful
GregV

Hi,
My guess is that Oracle from a SQL engine point of view here needs to determine that the expressions have the same datatype, so it needs to evaluate them. You can see this with this little piece of code:

declare
v_num number;

begin
select case when 1 = 2 then 'OK' else 0 end into v_num from dual;
end;
/

Error at line 1
ORA-06550: line 5, column 39:
PL/SQL: ORA-00932: inconsistent datatypes: expected CHAR got NUMBER
ORA-06550: line 5, column 1:
PL/SQL: SQL Statement ignored

Although the condition is false, it returns an error while 0 is numeric, but not 'OK'.
The PL/SQL Engine uses true short-circuit evaluation:

declare
v_num number;

begin
v_num := case when 1 = 2 then 'OK' else 0 end;
end;
/
PL/SQL procedure successfully completed.
mathguy
Answer

@gregv
My guess is that Oracle from a SQL engine point of view here needs to determine that the expressions have the same datatype, so it needs to evaluate them.

The first part is certainly correct, but the latter - very likely not.
In your example you are getting a compilation error, not a runtime error. The query parser is perfectly capable of determining data types without needing to evaluate the expressions. (If you created a procedure rather than an anonymous block, you would be able to see that the error is thrown at compilation time, not when the procedure is executed.)
Moreover, if anything was due to the need to evaluate ln_numbers(3), the out-of-bounds error would be thrown first, for obvious reasons. The runtime engine would not try to evaluate an array element with an index out of bounds (to confirm compatible data type or for any other reason) - the out-of-bounds error occurs before any evaluation attempt would ever be made.
Rather, SY's explanation seems perfectly plausible. Since the PL/SQL engine calls the SQL engine, it must pass arguments in the manner he explained, and this would trigger an evaluation of ln_numbers(3) (to be put on the call stack) before the evaluation of the case expression within the SQL context. Short-circuit evaluation happens too late for the attempt to evaluate an out-of-bounds array element to be ignored.

Marked as Answer by Miro Mas · Jul 9 2021
1 - 3

Post Details

Added on Jul 8 2021
3 comments
2,131 views
1 mention