Discussions
Categories
 384.6K All Categories
 2.5K Data
 556 Big Data Appliance
 1.9K Data Science
 451.4K Databases
 222.1K General Database Discussions
 3.8K Java and JavaScript in the Database
 32 Multilingual Engine
 562 MySQL Community Space
 479 NoSQL Database
 7.9K Oracle Database Express Edition (XE)
 3.1K ORDS, SODA & JSON in the Database
 561 SQLcl
 4K SQL Developer Data Modeler
 187.5K SQL & PL/SQL
 21.4K SQL Developer
 296.9K Development
 1 Application Development
 18 Developer Projects
 140 Programming Languages
 293.6K Development Tools
 112 DevOps
 3.1K QA/Testing
 646.2K Java
 28 Java Learning Subscription
 37K Database Connectivity
 193 Java Community Process
 106 Java 25
 22.1K Java APIs
 138.2K Java Development Tools
 165.3K Java EE (Java Enterprise Edition)
 21 Java Essentials
 167 Java 8 Questions
 86K Java Programming
 81 Java Puzzle Ball
 65.1K New To Java
 1.7K Training / Learning / Certification
 13.8K Java HotSpot Virtual Machine
 94.3K Java SE
 13.8K Java Security
 206 Java User Groups
 24 JavaScript  Nashorn
 Programs
 537 LiveLabs
 39 Workshops
 10.2K Software
 6.7K Berkeley DB Family
 3.5K JHeadstart
 5.7K Other Languages
 2.3K Chinese
 178 Deutsche Oracle Community
 1.1K Español
 1.9K Japanese
 236 Portuguese
Unexpected ORA06533: Subscript beyond count
If I run this script, I get ORA06553 :
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 ORA06533: 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

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.

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 outofbounds 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 outofbounds 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. Shortcircuit evaluation happens too late for the attempt to evaluate an outofbounds array element to be ignored.
Answers

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.

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 ORA06550: line 5, column 39: PL/SQL: ORA00932: inconsistent datatypes: expected CHAR got NUMBER ORA06550: 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 shortcircuit evaluation:
declare v_num number; begin v_num := case when 1 = 2 then 'OK' else 0 end; end; / PL/SQL procedure successfully completed.

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 outofbounds 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 outofbounds 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. Shortcircuit evaluation happens too late for the attempt to evaluate an outofbounds array element to be ignored.