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.
