ORA-06530: Reference to uninitialized composite

ORA-06530: Reference to uninitialized composite
Cause: An object, LOB, or other composite was referenced as a left hand side without having been initialized.
Action: Initialize the composite with an appropriate constructor or whole-object assignment.

Reference: Oracle Documentation

ORA-06530 exception is quite common exception in PL/SQL especially when programmers use collections (Associative Arrays or Nested Tables) of User defined Objects. In this blog post I will reproduce ORA-06530 exception and look at the ways to resolve ORA-06530.

Lets reproduce ORA-06530 with a very simple example:

SQL> create or replace type emp_obj_t is object
  2  (
  3  empno number,
  4  ename varchar2(100),
  5  sal number(10,2)
  6  );
  7  /
Type created.

analytic_lat@bidatadv> declare
  2    type emp_t is table of emp_obj_t index by binary_integer;
  3    emp_tab emp_t;
  4  begin
  5    emp_tab(1).empno := 3625;
  6    emp_tab(1).ename := 'Nimish Garg';
  7    emp_tab(1).sal := 62000;
  8  end;
  9  /
declare
*
ERROR at line 1:
ORA-06530: Reference to uninitialized composite
ORA-06512: at line 5

In above code, I have created a user defined object type and used that object type in my PL/SQL block to form a Associative array. But when I am trying to set attribute of object of an element in my array, it is throwing ORA-06530. ORA-06530 says that I am trying to reference an uninitialized object, but we do not need to initialized Associative Array. So what could be the issue?

Associative Array does not need initializing but the real issue is that since element of collection is an object so we can't reference its attribute without initializing object.

Solution is very simple, just to use object constructor to set the values.

declare 
  type emp_t is table of emp_obj_t index by binary_integer;
  emp_tab emp_t;
begin
  emp_tab(1) := emp_obj_t(3625, 'Nimish Garg', 62000);
end;
/

We can also refer object attributes by their names like.

DECLARE
   TYPE emp_t IS TABLE OF emp_obj_t
      INDEX BY BINARY_INTEGER;

   emp_tab   emp_t;
BEGIN
   emp_tab (1) := emp_obj_t (
                        empno => 3625, 
                        ename => 'Nimish Garg', 
                        sal => 62000);
END;
/

Or simply use the object constructor to set attributes as null then set the values later.

declare 
  type emp_t is table of emp_obj_t index by binary_integer;
  emp_tab emp_t;
begin
  emp_tab(1) := emp_obj_t(null, null, null);
  emp_tab(1).empno := 3625;
  emp_tab(1).ename := 'Nimish Garg';  
  emp_tab(1).sal := 62000;  
end;
/

If we are using nested tables, solution is almost same but because it is nested table, we need to initialize it and then extend it. I prefer to get the last index by LAST(), because Nested Tables can be sparsed.

declare 
  type emp_t is table of emp_obj_t;
  emp_tab emp_t;
  i number;
begin
  emp_tab := emp_t();
  emp_tab.extend;
  i := emp_tab.last;
  
  emp_tab(i) := emp_obj_t(null, null, null);
  emp_tab(i).empno := 3625;
  emp_tab(i).ename := 'Nimish Garg';  
  emp_tab(i).sal := 62000;  
end;
/

I hope you have enjoyed reading this even if you knew the solution. Please use comment box to provide your feedback.

Related Posts:
- Oracle Database 12c New Features for Developers
- ORA-02303: cannot drop or replace a type with type or table dependents
- PLSQL Tuning: Bulk Collect with Dynamic SQL
- PLSQL Tuning: Bulk Collect with Dynamic SQL
- Articles on Oracle PL/SQL