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

15 comments:

  1. very nice article...!!

    ReplyDelete
  2. Hi Nimish,

    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;
    /

    Where this values are stored? How can I see this values? Please explain.

    Regds

    ReplyDelete
    Replies
    1. Values are stored in memory allocated to this collection (like variable), if you want to get the value back from collection, you can use collection(index).attribute notation. like
      dbms_output.put_line(emp_tab(1).empno);

      Delete
    2. Thanks alot .. working fine. But small request ... Inserting values like this..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', 6200);
      emp_tab(2) := emp_obj_t(3626, 'abc', 6500);
      emp_tab(3) := emp_obj_t(3627, 'xyz', 7000);
      dbms_output.put_line(emp_tab(1).empno||emp_tab(1).ename||emp_tab(1).sal);
      dbms_output.put_line(emp_tab(2).empno||emp_tab(2).ename||emp_tab(2).sal);
      dbms_output.put_line(emp_tab(3).empno||emp_tab(3).ename||emp_tab(3).sal);
      end;

      insert emp table records into these 3 columns and retrieve records with loop is best .. can you please give me one example..it's very nice of you.

      Regards

      Delete
    3. better use bulk collect for that

      Delete
  3. Nice one garg.

    ReplyDelete
  4. I really loved to read this article..thanks for shared your knowledge.

    ReplyDelete
  5. Hi Nimish,

    I want to ask one question regarding Collections.
    As per Oracle the collections are much faster than relational tables as they are part of PGA.
    The question is, when/where should I use them?
    Is it possible to give an real life example/case in which we can use Collections?

    ReplyDelete
    Replies
    1. IMHO, you should only use plsql features for the problems you can not solve in SQL. SQL is the fastest language available for data related problems. I use plsql to wrap my sql mostly, nothing else.

      Delete
  6. This comment has been removed by the author.

    ReplyDelete
  7. Hi Nimish,

    Your articles are great! Very easy to understand.

    I am facing issue in below scenario,

    declare
    type emp_t is table of emp_obj_t;
    emp_tab emp_t;
    i number;
    begin

    FOR ind in (select * from employees)
    LOOP
    emp_tab := emp_t();
    emp_tab.extend;
    i := ind + 1;

    emp_tab(i) := emp_obj_t(null, null, null);
    emp_tab(i).empno := i.empno;
    emp_tab(i).ename := i.ename;
    emp_tab(i).sal := i.sal;
    END LOOP
    end;
    /

    And when I execute above it gives,

    Error Code: -6533
    Error Message: ORA-06533: Subscript beyond count

    Kindly help. Thanks in advance.

    ReplyDelete
  8. Hi Nimish!
    Simply too good, well explained...without complex examples which confuse one all the more.

    Keep up the great work!

    ReplyDelete
  9. The following code gives me an ORA-06530: Reference to uninitialized composite

    CREATE OR REPLACE TYPE multi_values_ot AS OBJECT
    (
    string_param VARCHAR2(255) ,
    remarks VARCHAR2(255)
    );

    CREATE OR REPLACE TYPE multi_values_nt
    IS TABLE OF multi_values_ot;

    drop type multi_values_nt;

    CREATE OR REPLACE FUNCTION f_check_string_validity(p_string VARCHAR2)
    RETURN multi_values_nt
    IS
    var_num_1 NUMBER:=0;
    var_num_2 NUMBER:=0;
    l_return multi_values_nt ;
    i NUMBER ;

    CURSOR cur_rec (var_string VARCHAR2)
    IS
    select regexp_substr(var_string,'[^,]+', 1, level)
    from dual
    connect by regexp_substr(var_string, '[^,]+', 1, level) is not null;
    BEGIN


    l_return := multi_values_nt();
    l_return.EXTEND;
    i := l_return.last;
    l_return(i) := multi_values_ot(null,null);
    l_return(i).string_param := null;
    l_return(i).remarks := null;

    OPEN cur_rec(p_string);
    LOOP
    l_return.EXTEND;
    FETCH cur_rec INTO l_return(l_return.LAST).string_param;
    EXIT WHEN cur_rec%NOTFOUND;
    END LOOP;
    CLOSE cur_rec;
    FOR l_index IN l_return.FIRST..l_return.LAST
    LOOP
    --dbms_output.put_line(ParamTabTyp(l_index));
    IF LENGTH(TRIM(TRANSLATE(l_return(l_index).string_param,'-0123456789', ' '))) IS NULL THEN
    IF l_return(l_index).string_param LIKE '%-%' THEN
    var_num_1 := TO_NUMBER(SUBSTR(l_return(l_index).string_param,1,INSTR(l_return(l_index).string_param,'-',1) - 1));
    var_num_2 := TO_NUMBER(SUBSTR(l_return(l_index).string_param,INSTR(l_return(l_index).string_param,'-',1) + 1));
    IF var_num_1 > var_num_2 THEN
    l_return(l_index).remarks := 'Num1 is greater than Num2';
    END IF;
    END IF;
    ELSIF LENGTH(TRIM(TRANSLATE(l_return(l_index).string_param,'-0123456789', ' '))) > 0 THEN
    l_return(l_index).remarks := 'Input parameter contains invalid character';
    END IF;
    END LOOP;
    RETURN l_return;
    END;

    SELECT * from table (f_check_string_validity('1000*3000,4000-3000,200,100,300'));

    ReplyDelete
  10. Nice article with simple example. Thank you!

    ReplyDelete