Discussions
Categories
 382.6K All Categories
 2.1K Data
 213 Big Data Appliance
 1.9K Data Science
 448.5K Databases
 221.1K General Database Discussions
 3.7K Java and JavaScript in the Database
 25 Multilingual Engine
 541 MySQL Community Space
 469 NoSQL Database
 7.8K Oracle Database Express Edition (XE)
 2.9K ORDS, SODA & JSON in the Database
 503 SQLcl
 3.9K SQL Developer Data Modeler
 186.3K SQL & PL/SQL
 21.1K SQL Developer
 293.9K Development
 9 Developer Projects
 130 Programming Languages
 290.6K Development Tools
 97 DevOps
 3K QA/Testing
 645.6K Java
 24 Java Learning Subscription
 36.9K Database Connectivity
 151 Java Community Process
 104 Java 25
 22.1K Java APIs
 137.9K Java Development Tools
 165.3K Java EE (Java Enterprise Edition)
 17 Java Essentials
 146 Java 8 Questions
 85.9K Java Programming
 79 Java Puzzle Ball
 65.1K New To Java
 1.7K Training / Learning / Certification
 13.8K Java HotSpot Virtual Machine
 94.2K Java SE
 13.8K Java Security
 201 Java User Groups
 24 JavaScript  Nashorn
 Programs
 299 LiveLabs
 36 Workshops
 10.2K Software
 6.7K Berkeley DB Family
 3.5K JHeadstart
 5.8K Other Languages
 2.3K Chinese
 168 Deutsche Oracle Community
 1.2K Español
 1.9K Japanese
 235 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.