ORA-06531: Reference to uninitialized collection troubleshooting

Error message:

ORA-06531: Reference to uninitialized collection

Error SQL code:

Declare
TYPE t_student_var is table of VARCHAR2 (100 );

V_tbl_name t_student_var;
Begin
Select name into v_tbl_name (1) from t_student where gid = 1;
Select name into v_tbl_name (2) from t_student where gid = 2;
Select name into v_tbl_name (3) from t_student where gid = 3;
Dbms_output.put_line (v_tbl_name (1 ));
Dbms_output.put_line (v_tbl_name (2 ));
Dbms_output.put_line (v_tbl_name (3 ));
End;

Problem Analysis:

Oracle custom type Syntax:

TYPE type_name is table of element_type index by [BINARY_INTEGER | PLS_INTEGER | VARRAY2]; where: index by: This statement increases the Number TYPE subscript, automatically initializes, and allocates space, with this statement, you do not need to display initialization or allocate space through extend when inserting elements into a table record. Both Binary_Integer and Pls_Integer are integer types.

The Calculation of Binary_Integer variable values is performed by Oracle without Overflow, but the execution speed is slow because it is simulated by Oracle. The execution of Pls_Integer is performed by the hardware, that is, directly by the CPU. Therefore, overflow occurs, but the execution speed is much faster than that of Pls_Integer.

The preceding instructions show that there are two solutions:

1. When defining the record table type, add the index by statement. The modification is as follows:

Declare
TYPE t_student_var is table of VARCHAR2 (100) index by BINARY_INTEGER;

V_tbl_name t_student_var;
Begin
Select name into v_tbl_name (1) from t_student where gid = 1;
Select name into v_tbl_name (2) from t_student where gid = 2;
Select name into v_tbl_name (3) from t_student where gid = 3;
Dbms_output.put_line (v_tbl_name (1 ));
Dbms_output.put_line (v_tbl_name (2 ));
Dbms_output.put_line (v_tbl_name (3 ));
End;

This method is relatively simple. We recommend that you use this method.

2. initialize and use the extend statement to expand the space. The modification is as follows:

Declare
TYPE t_student_var is table of VARCHAR2 (100 );

V_tbl_name t_student_var: = t_student_var (); -- initialize
Begin
V_tbl_name.extend; -- Extended Space
Select name into v_tbl_name (1) from t_student where gid = 1;
V_tbl_name.extend; -- Extended Space
Select name into v_tbl_name (2) from t_student where gid = 2;
V_tbl_name.extend; -- Extended Space
Select name into v_tbl_name (3) from t_student where gid = 3;
Dbms_output.put_line (v_tbl_name (1 ));
Dbms_output.put_line (v_tbl_name (2 ));
Dbms_output.put_line (v_tbl_name (3 ));
End;

In addition, extend (n) is used to expand n spaces at a time, so the following code has the same effect as the above Code:

Declare
TYPE t_student_var is table of VARCHAR2 (100 );

V_tbl_name t_student_var: = t_student_var (); -- initialize
Begin
V_tbl_name.extend (3); -- expand three spaces
Select name into v_tbl_name (1) from t_student where gid = 1;
Select name into v_tbl_name (2) from t_student where gid = 2;
Select name into v_tbl_name (3) from t_student where gid = 3;
Dbms_output.put_line (v_tbl_name (1 ));
Dbms_output.put_line (v_tbl_name (2 ));
Dbms_output.put_line (v_tbl_name (3 ));
End;

Oracle ORA-01555 snapshot old description

Troubleshooting for ORA-01078 and LRM-00109

ORA-01555 ultra-long Query Duration time

Notes on ORA-00471 Processing Methods

ORA-00314, redolog corruption, or missing Handling Methods

Solution to ORA-00257 archive logs being too large to store