Forum Stats

  • 3,855,310 Users
  • 2,264,499 Discussions
  • 7,905,967 Comments

Discussions

Categories

Unexpected ORA-06533: Subscript beyond count

Miro Mas
Miro Mas Member Posts: 1 Green Ribbon
edited Jul 8, 2021 12:23PM in SQL & PL/SQL

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


· Share on TwitterShare on Facebook

Best Answers

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,729 Red Diamond
    edited Jul 8, 2021 1:02PM Answer ✓

    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.

    mathguy
    · Share on TwitterShare on Facebook
  • mathguy
    mathguy Member Posts: 10,693 Blue Diamond
    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.

    · Share on TwitterShare on Facebook

Answers

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,729 Red Diamond
    edited Jul 8, 2021 1:02PM Answer ✓

    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.

    mathguy
    · Share on TwitterShare on Facebook
  • GregV
    GregV Member Posts: 3,099 Gold Crown
    edited Jul 8, 2021 1:15PM

    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.
    · Share on TwitterShare on Facebook
  • mathguy
    mathguy Member Posts: 10,693 Blue Diamond
    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.

    · Share on TwitterShare on Facebook