Skip to Main Content

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

Post Details

Added on Jul 8 2021
3 comments
2,268 views