How to Fix ORA-06531: Reference to Uninitialized Collection in Oracle

ORA-06531 is raised when PL/SQL code uses a nested table or varray variable that was declared but never initialized. Such a variable starts atomically null: it is not an empty collection, it is no collection at all, so EXTEND, COUNT, and element access all fail. The predefined exception is COLLECTION_IS_NULL. Initialize the variable with its constructor, or fill it with BULK COLLECT.

Reproduce the Error

Example:

declare
  type num_list is table of number;
  v_list num_list;                       -- declared, but atomically null
begin
  v_list.extend;
end;
/
declare
  type num_list is table of number;
  v_list num_list;
begin
  dbms_output.put_line(v_list.count);
end;
/

Output:

declare
*
ERROR at line 1:
ORA-06531: Reference to uninitialized collection
ORA-06512: at line 5

declare
*
ERROR at line 1:
ORA-06531: Reference to uninitialized collection
ORA-06512: at line 5

Both EXTEND and COUNT fail on the null collection.

Fix: Initialize the Collection

Example:

set serveroutput on
declare
  type num_list is table of number;
  v_list num_list := num_list();         -- empty, not null
begin
  v_list.extend;
  v_list(1) := 42;
  dbms_output.put_line('Count: ' || v_list.count);
end;
/
declare
  type num_list is table of number;
  v_list num_list;
begin
  -- BULK COLLECT initializes the collection
  select level bulk collect into v_list from dual connect by level <= 3;
  dbms_output.put_line('Count: ' || v_list.count);
end;
/

Output:

Count: 1

PL/SQL procedure successfully completed.

Count: 3

PL/SQL procedure successfully completed.

• NUM_LIST() creates an empty collection, ready for EXTEND.
• BULK COLLECT INTO initializes the collection itself, even when the query returns no rows.

Things to Know

• Associative arrays (INDEX BY tables) never need initialization and cannot raise ORA-06531.
• Test a collection with IS NULL before using it when it comes from a parameter or a column.
• Assigning NULL to a collection makes it atomically null again; use DELETE to empty it instead.

Related Guides

• How to Use Nested Tables in PL/SQL
• How to Fix ORA-06530: Reference to Uninitialized Composite in Oracle

Conclusion

ORA-06531 means a nested table or varray was used before it existed. Initialize it with its constructor at declaration, or fill it with BULK COLLECT, and empty it with DELETE rather than NULL.