Forum Stats

  • 3,874,584 Users
  • 2,266,762 Discussions
  • 7,911,903 Comments

Discussions

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


Best Answers

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,947 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
  • mathguy
    mathguy Member Posts: 10,900 Black 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.

Answers

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,947 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
  • GregV
    GregV Member Posts: 3,106 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.
    
  • mathguy
    mathguy Member Posts: 10,900 Black 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.