PL/SQL Integrated Problem Solving: ORA-06533: The following table exceeds quantity

VARRAY is a type introduced when Oracle8G is used. It is a dense array, which is easier to be understood as a traditional array. When creating these arrays

VARRAY is a type introduced when Oracle8G is used. It is a dense array, which is easier to be understood as a traditional array. When creating these arrays

VARRAY is a type introduced when Oracle8G is used. It is a dense array, which is easier to be understood as a traditional array. When creating these arrays, they all have a size. Note that the following table of VARRAY starts from 1. In many other languages, the following table of the array starts from 0, for example, JAVA. In practice, beginners often encounter the following error messages:

ORA-06533: The following table exceeds

For example, this error occurs in the following code:

DECLARE
TYPE NUM_VARRAY is varray (100) of number not null;
V_NUM_VARRAY NUM_VARRAY: = NUM_VARRAY ();
BEGIN
For j in 1 .. 100 LOOP
V_NUM_VARRAY (J): = J;
End loop;
For I IN 1 .. V_NUM_VARRAY.COUNT LOOP
DBMS_OUTPUT.PUT_LINE (TO_CHAR (V_NUM_VARRAY (I )));
End loop;
END;

What is the cause of this error? We have defined an empty VARRAY. In the first J loop, 1 is assigned to V_NUM_ARRAY (1), but in fact, there is no storage space for this set variable, therefore, PL/SQL reports an error "the number of tables exceeds the limit.

How can we solve this problem? Refer to Oracle's MOS and find the following description:

EXTEND Collection Method

EXTEND is a procedure that adds elements to the end of a varray or nested table. the collection can be empty, but not null. (To make a collection empty or add elements to a null collection, use a constructor. for more information, see .)

The EXTEND method has these forms:

EXTEND appends one null element to the collection.

EXTEND (n) appends n null elements to the collection.

EXTEND (n, I) appends n copies of the ith element to the collection.

Note:

EXTEND (n, I) is the only form that you can use for a collection whose elements have the NOT NULL constraint.

EXTEND operates on the internal size of a collection. That is, if DELETE deletes an element but keeps a placeholder for it, then EXTEND considers the element to exist.

Declares a nested table variable, initializing it with three elements; appends two copies of the first element; deletes the th (last) element; and then appends one null element. because EXTEND considers the deleted th element to exist, the appended null element is the sixth element. the procedureprint_nt prints the nested table variable after initialization and after the EXTEND and DELETE operations. the type nt_type and procedure print_nt are defined in.

Example 5-20 EXTEND Method with Nested Table

DECLARE nt nt_type: = nt_type (11, 22, 33); BEGIN print_nt (nt); nt. EXTEND (2, 1); -- Append two copies of first element print_nt (nt); nt. DELETE (5); -- Delete th element print_nt (nt); nt. EXTEND; -- Append one null element print_nt (nt); END ;/

Result:

Nt. (1) = 11nt. (2) = 22nt. (3) = 33 --- nt. (1) = 11nt. (2) = 22nt. (3) = 33nt. (4) = 11nt. (5) = 11 --- nt. (1) = 11nt. (2) = 22nt. (3) = 33nt. (4) = 11 --- nt. (1) = 11nt. (2) = 22nt. (3) = 33nt. (4) = 11nt. (6) = NULL ---

The above principle is:

The EXTEND method allocates storage space for one or more new elements in the VARRAY or nested table set. There are two options available. By default, it allocates storage space for this value before adding a value to the set. The two parameters respectively mean that the first parameter is to increase the space of the elements, and the second parameter is to reference the indexes of existing elements in the collection, this element will be copied to the new element.

The error code above is changed to the following:

DECLARE
TYPE NUM_VARRAY is varray (100) of number not null;
V_NUM_VARRAY NUM_VARRAY: = NUM_VARRAY ();
BEGIN
For j in 1 .. 100 LOOP
V_NUM_VARRAY.EXTEND;
V_NUM_VARRAY (J): = J;
End loop;
For I IN 1 .. V_NUM_VARRAY.COUNT LOOP
DBMS_OUTPUT.PUT_LINE (TO_CHAR (V_NUM_VARRAY (I )));
End loop;
END;