Tuesday, June 30, 2009

ORA-06532: Subscript outside of limit





This error is returned if, for some reason, a subscript value is lower than 1 (one) or greater than the declared upper bound of a varray.
A negative or zero value subscript will also cause an error when working with nested tables.
Do not confuse the upper bound with the actual number of initialized elements in the varray or table. A varray may contain 5 elements out of a maximum of 10, so if you specify 6 as subscript, the error returned will be different.

Let's look at a couple of simple situations:

DECLARE
TYPE array_type IS VARRAY(10) OF VARCHAR2(200);
my_array array_type := array_type(null, null, null);
BEGIN
my_array(0) := 'a';
dbms_output.enable;
dbms_output.put_line(my_array.COUNT);
END;

Error report:
ORA-06532: Subscript outside of limit
ORA-06512: at line 5
06532. 00000 - "Subscript outside of limit"
*Cause: A subscript was greater than the limit of a varray
or non-positive for a varray or nested table.
*Action: Check the program logic and increase the varray limit
if necessary.
Another case being:
DECLARE
TYPE array_type IS VARRAY(10) OF VARCHAR2(200);
my_array array_type := array_type(null, null, null);
BEGIN
my_array(11) := 'a';
dbms_output.enable;
dbms_output.put_line(my_array.COUNT);
END;

Error report:
ORA-06532: Subscript outside of limit
ORA-06512: at line 5
06532. 00000 - "Subscript outside of limit"
*Cause: A subscript was greater than the limit of a varray
or non-positive for a varray or nested table.
*Action: Check the program logic and increase the varray limit
if necessary.

It should be clear that in both situations the subscript is out of range.
A slightly different situation is the following one:
DECLARE
TYPE array_type IS VARRAY(10) OF VARCHAR2(200);
my_array array_type := array_type();
BEGIN
my_array.EXTEND(11);
dbms_output.enable;
dbms_output.put_line(my_array.COUNT);
END;

Error report:
ORA-06532: Subscript outside of limit
ORA-06512: at line 5
06532. 00000 - "Subscript outside of limit"
*Cause: A subscript was greater than the limit of a varray
or non-positive for a varray or nested table.
*Action: Check the program logic and increase the varray limit
if necessary.
In this case it's easy to spot that we tried to initialize the collection to a larger number of elements that it can hold, but there can be subtler situations as follows:
DECLARE
TYPE array_type IS VARRAY(10) OF VARCHAR2(200);
my_array array_type := array_type(null);
BEGIN
my_array.EXTEND(10);
dbms_output.enable;
dbms_output.put_line(my_array.COUNT);
END;
It would be fine to extend the varray by 10 elements if we hadn't already initialized one element at declaration time (the null element above), indeed if you remove the null from the type constructor, the program will run without errors.

If you are populating the collection by means of some iterative process where you extend (that is initialize) the elements one at a time, then you must ensure that you do not extend the varray beyond its limits.

1 comment:

Johnny said...

Hi Karthik, this is really great. It helped me alot