ORA-06530: Reference to Uninitialized Composite - Initialize PL/SQL Collections

ORA-06530: Reference to Uninitialized Composite

Error Overview

Error Text: ORA-06530: Reference to uninitialized composite

The ORA-06530 error is raised at runtime when PL/SQL code attempts to use a collection (nested table or VARRAY) or an object type variable that has not been initialized. In PL/SQL, declaring a collection or object type variable only creates the variable — it does not create the object itself. The variable starts as NULL (atomically null), and any attempt to access attributes, call methods, or use collection operators before initialization causes ORA-06530.

This error does not affect associative arrays (index-by tables), which can be used without explicit initialization. It is exclusive to nested tables, VARRAYs, and SQL object types.

Common Causes

1. Nested Table Not Initialized with Constructor

• Variable declared but constructor not called (type_name() syntax missing)
• Collection variable set to NULL explicitly and then accessed
• Collection passed as an OUT parameter that the caller never initialized

2. VARRAY Not Initialized Before Element Assignment

• VARRAY declared at the package or procedure level without initialization
• Attempting to extend or access a VARRAY before calling its constructor
• VARRAY returned from a function and used directly without null check

3. Object Type Variable Used Without Constructor

• SQL object type instantiated with NULL assignment then attribute accessed
• Object type returned from a function that returned NULL
• Object type column fetched from a row where it is NULL, then attribute accessed

4. Collection Assigned NULL in Logic Branch

• A conditional branch sets the collection to NULL and subsequent code assumes it is initialized
• Exception handler sets collection to NULL as a reset but code continues to use it
• Global package-level collection reset to NULL during a session-level reset routine

5. Improper Use in SQL Context

• Nested table used in a TABLE() cast without being initialized in the SELECT context
• Passing an uninitialized collection to a pipelined function
• Using a collection in a FORALL statement without populating it first

Diagnostic Queries

Identify Invalid or Problematic Object Types

-- Find all collection and object types in the database
SELECT
  owner,
  type_name,
  typecode,
  attributes,
  methods,
  predefined,
  incomplete,
  final,
  instantiable
FROM dba_types
WHERE typecode IN ('COLLECTION', 'OBJECT')
  AND owner NOT IN ('SYS', 'SYSTEM', 'ORDSYS', 'MDSYS', 'XDB')
ORDER BY owner, typecode, type_name;

-- Find nested table and VARRAY type details
SELECT
  owner,
  type_name,
  coll_type,
  upper_bound,   -- NULL for nested tables, defined for VARRAYs
  elem_type_owner,
  elem_type_name,
  length,
  precision,
  scale
FROM dba_coll_types
WHERE owner NOT IN ('SYS','SYSTEM','ORDSYS','MDSYS','XDB')
ORDER BY owner, coll_type, type_name;

Find PL/SQL Objects That Use Collection Types

-- Find procedures/packages referencing a specific collection type
SELECT DISTINCT
  d.owner,
  d.name,
  d.type
FROM dba_dependencies d
WHERE d.referenced_name = 'YOUR_COLLECTION_TYPE'
  AND d.referenced_owner = 'YOUR_SCHEMA'
  AND d.type IN ('PROCEDURE','FUNCTION','PACKAGE','PACKAGE BODY','TRIGGER')
ORDER BY d.type, d.name;

-- Find PL/SQL source containing collection declarations
-- (search for the type name in source code)
SELECT
  owner,
  name,
  type,
  line,
  text
FROM dba_source
WHERE UPPER(text) LIKE UPPER('%YOUR_TYPE_NAME%')
  AND owner = 'YOUR_SCHEMA'
ORDER BY name, line;

Trace the Error to Source Line

-- ORA-06530 comes with ORA-06512 showing the line number.
-- Enable error tracing for a session:
ALTER SESSION SET EVENTS '6530 trace name errorstack level 3';

-- After error occurs, find the trace file:
SELECT value FROM v$diag_info WHERE name = 'Default Trace File';

-- Review compilation errors for recently changed objects
SELECT
  owner,
  name,
  type,
  line,
  position,
  text
FROM dba_errors
WHERE owner = 'YOUR_SCHEMA'
ORDER BY name, sequence;

Check for NULL Object Columns in Tables

-- Find rows where an object type column is NULL
-- (fetching NULL objects and accessing their attributes causes ORA-06530)
SELECT COUNT(*) as null_object_rows
FROM your_table
WHERE your_object_column IS NULL;

-- Preview the null rows
SELECT *
FROM your_table
WHERE your_object_column IS NULL
FETCH FIRST 10 ROWS ONLY;

Step-by-Step Resolution

1. Initialize Nested Tables with a Constructor

-- BAD: Variable declared but not initialized
DECLARE
  TYPE t_emp_list IS TABLE OF VARCHAR2(100);
  l_employees t_emp_list;   -- This is NULL (atomically null)
BEGIN
  l_employees.EXTEND;       -- ORA-06530: Reference to uninitialized composite
  l_employees(1) := 'John';
END;

-- GOOD: Initialize with the constructor before use
DECLARE
  TYPE t_emp_list IS TABLE OF VARCHAR2(100);
  l_employees t_emp_list := t_emp_list();  -- Empty but initialized
BEGIN
  l_employees.EXTEND;
  l_employees(1) := 'John';
  DBMS_OUTPUT.PUT_LINE('Count: ' || l_employees.COUNT);
END;
/

2. Initialize VARRAYs Correctly

-- BAD: VARRAY not initialized
DECLARE
  TYPE t_top_scores IS VARRAY(10) OF NUMBER;
  l_scores t_top_scores;   -- NULL
BEGIN
  l_scores.EXTEND;         -- ORA-06530
  l_scores(1) := 100;
END;

-- GOOD: Initialize the VARRAY with its constructor
DECLARE
  TYPE t_top_scores IS VARRAY(10) OF NUMBER;
  l_scores t_top_scores := t_top_scores();  -- Initialized, empty
BEGIN
  l_scores.EXTEND(3);
  l_scores(1) := 100;
  l_scores(2) := 95;
  l_scores(3) := 88;
  DBMS_OUTPUT.PUT_LINE('Element 1: ' || l_scores(1));
END;
/

-- GOOD: Initialize VARRAY with values directly
DECLARE
  TYPE t_top_scores IS VARRAY(10) OF NUMBER;
  l_scores t_top_scores := t_top_scores(100, 95, 88);
BEGIN
  DBMS_OUTPUT.PUT_LINE('Count: ' || l_scores.COUNT);
END;
/

3. Initialize SQL Object Types

-- Given a SQL object type:
CREATE OR REPLACE TYPE address_t AS OBJECT (
  street  VARCHAR2(100),
  city    VARCHAR2(50),
  state   VARCHAR2(2),
  zip     VARCHAR2(10)
);
/

-- BAD: Accessing attributes on a NULL object
DECLARE
  l_addr address_t;        -- NULL object
BEGIN
  l_addr.city := 'Austin'; -- ORA-06530
END;

-- GOOD: Call the constructor first
DECLARE
  l_addr address_t := address_t(NULL, NULL, NULL, NULL);
BEGIN
  l_addr.street := '123 Main St';
  l_addr.city   := 'Austin';
  l_addr.state  := 'TX';
  l_addr.zip    := '78701';
  DBMS_OUTPUT.PUT_LINE(l_addr.city || ', ' || l_addr.state);
END;
/

4. Guard Against NULL Collections from Function Returns

-- Safely handle collections returned from functions that may return NULL
DECLARE
  TYPE t_id_list IS TABLE OF NUMBER;
  l_ids t_id_list;
BEGIN
  l_ids := get_active_ids();   -- Function may return NULL

  -- Always check for NULL before using the collection
  IF l_ids IS NULL THEN
    l_ids := t_id_list();      -- Initialize to empty if NULL returned
  END IF;

  IF l_ids.COUNT > 0 THEN
    FOR i IN 1..l_ids.COUNT LOOP
      DBMS_OUTPUT.PUT_LINE('ID: ' || l_ids(i));
    END LOOP;
  ELSE
    DBMS_OUTPUT.PUT_LINE('No IDs returned.');
  END IF;
END;
/

5. Handle NULL Object Type Columns Fetched from the Database

-- BAD: Directly accessing attributes without null check
DECLARE
  l_addr  address_t;
  l_name  VARCHAR2(100);
BEGIN
  SELECT address INTO l_addr FROM customers WHERE customer_id = 42;
  l_name := l_addr.city;   -- ORA-06530 if address column is NULL
END;

-- GOOD: Check for NULL object before accessing attributes
DECLARE
  l_addr  address_t;
  l_city  VARCHAR2(50);
BEGIN
  SELECT address INTO l_addr FROM customers WHERE customer_id = 42;

  IF l_addr IS NOT NULL THEN
    l_city := l_addr.city;
    DBMS_OUTPUT.PUT_LINE('City: ' || l_city);
  ELSE
    DBMS_OUTPUT.PUT_LINE('No address on file.');
  END IF;
END;
/

6. Fix Package-Level Collection Variables

-- Package with collection initialized at package level
CREATE OR REPLACE PACKAGE employee_cache AS
  TYPE t_emp_tab IS TABLE OF employees%ROWTYPE;
  g_employees t_emp_tab;   -- This is NULL until initialized

  PROCEDURE load_cache;
  PROCEDURE clear_cache;
END employee_cache;
/

CREATE OR REPLACE PACKAGE BODY employee_cache AS
  PROCEDURE load_cache AS
  BEGIN
    -- Always initialize before populating
    g_employees := t_emp_tab();

    SELECT * BULK COLLECT INTO g_employees
    FROM employees
    WHERE active_flag = 'Y';
  END load_cache;

  PROCEDURE clear_cache AS
  BEGIN
    g_employees := t_emp_tab();  -- Reset to empty, not NULL
    -- Never set to NULL: g_employees := NULL;  -- causes ORA-06530 on next use
  END clear_cache;
END employee_cache;
/

7. Safe Use in FORALL and BULK COLLECT

-- Safe BULK COLLECT and FORALL pattern
DECLARE
  TYPE t_id_list   IS TABLE OF NUMBER;
  TYPE t_name_list IS TABLE OF VARCHAR2(100);
  l_ids   t_id_list   := t_id_list();   -- Always initialize
  l_names t_name_list := t_name_list(); -- Always initialize
BEGIN
  -- BULK COLLECT automatically extends the collection
  SELECT employee_id, last_name
  BULK COLLECT INTO l_ids, l_names
  FROM employees
  WHERE department_id = 10;

  IF l_ids.COUNT > 0 THEN
    FORALL i IN 1..l_ids.COUNT
      UPDATE employees
      SET    last_name = UPPER(l_names(i))
      WHERE  employee_id = l_ids(i);

    COMMIT;
    DBMS_OUTPUT.PUT_LINE('Updated ' || SQL%ROWCOUNT || ' rows.');
  END IF;
END;
/

Prevention Strategies

1. Defensive Initialization Pattern

-- Create a utility function to safely initialize any collection
CREATE OR REPLACE PACKAGE collection_utils AS
  -- Returns TRUE if the collection is initialized and not empty
  FUNCTION is_usable(p_count IN INTEGER) RETURN BOOLEAN;
END collection_utils;
/

CREATE OR REPLACE PACKAGE BODY collection_utils AS
  FUNCTION is_usable(p_count IN INTEGER) RETURN BOOLEAN AS
  BEGIN
    -- COUNT raises ORA-06530 on uninitialized collections,
    -- so wrap COUNT calls in exception handlers in untrusted code paths
    RETURN (p_count > 0);
  EXCEPTION
    WHEN OTHERS THEN
      RETURN FALSE;
  END is_usable;
END collection_utils;
/

2. Code Review Checklist for PL/SQL Collections

-- Audit: find declarations of nested table/VARRAY types in source
-- without accompanying constructor initialization on the same or next line
SELECT
  s.owner,
  s.name,
  s.type,
  s.line,
  s.text
FROM dba_source s
WHERE s.owner = 'YOUR_SCHEMA'
  AND UPPER(s.text) LIKE '% TABLE OF %'
  AND s.type IN ('PROCEDURE','FUNCTION','PACKAGE BODY')
ORDER BY s.name, s.line;

3. Best Practices for PL/SQL Collection Safety

• Always initialize nested tables and VARRAYs at the point of declaration using the constructor: TYPE_NAME()
• For package-level collections, initialize in the package body initialization block (the BEGIN section after all procedure/function bodies)
• Never set a collection variable to NULL as a “reset” — set it to an empty constructor call instead: l_col := my_type()
• Always check IF l_collection IS NOT NULL AND l_collection.COUNT > 0 THEN before iterating
• For object type columns fetched from the database, always check IF l_object IS NOT NULL THEN before accessing attributes
• Use associative arrays (INDEX BY PLS_INTEGER) when you do not need SQL-level collection operations — they never raise ORA-06530

Diagnostic Scripts

These Oracle Day by Day scripts support PL/SQL and schema investigation:

• health.sql — Database health check including invalid object counts
• dtable.sql — Table structure and column type analysis

Related Errors

• ORA-06512 - At line (PL/SQL error stack backtrace, always accompanies ORA-06530)
• ORA-06508 - PL/SQL: could not find program unit being called
• ORA-04065 - Not executed, altered or dropped stored procedure
• ORA-01403 - No data found (common companion when fetching object type columns)

Emergency Response

Quick Fixes

1. Pinpoint the line number from the error stack

   ORA-06530 is always accompanied by ORA-06512 stack entries that identify the exact file, package, and line number. Read the full error message before making any changes.

2. Add initialization to the declaration

   -- Change this:
   l_my_list   my_nested_table_type;
   -- To this:
   l_my_list   my_nested_table_type := my_nested_table_type();

3. Add a NULL guard before accessing the collection

   IF l_my_collection IS NOT NULL THEN
     -- safe to use COUNT, EXTEND, FIRST, etc.
   END IF;

Post-Resolution Cleanup

-- After fixing the code, recompile the affected object
ALTER PROCEDURE your_schema.your_procedure COMPILE;
ALTER PACKAGE   your_schema.your_package   COMPILE BODY;

-- Confirm the object is valid
SELECT object_name, object_type, status
FROM dba_objects
WHERE owner       = 'YOUR_SCHEMA'
  AND object_name = 'YOUR_OBJECT'
  AND status      = 'INVALID';

-- Verify no compilation errors remain
SELECT line, position, text
FROM dba_errors
WHERE owner = 'YOUR_SCHEMA'
  AND name  = 'YOUR_OBJECT'
ORDER BY sequence;