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:
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.
We can also refer object attributes by their names like.
Or simply use the object constructor to set attributes as null then set the values later.
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.
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
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
very nice article...!!
ReplyDeleteHi Nimish,
ReplyDeletedeclare
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
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
Deletedbms_output.put_line(emp_tab(1).empno);
Thanks alot .. working fine. But small request ... Inserting values like this..declare
Deletetype 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
better use bulk collect for that
DeleteNice one garg.
ReplyDeleteI really loved to read this article..thanks for shared your knowledge.
ReplyDeletenice explanation
ReplyDeleteHi Nimish,
ReplyDeleteI 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?
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.
DeleteThis comment has been removed by the author.
ReplyDeleteHi Nimish,
ReplyDeleteYour 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.
Hi Nimish!
ReplyDeleteSimply too good, well explained...without complex examples which confuse one all the more.
Keep up the great work!
The following code gives me an ORA-06530: Reference to uninitialized composite
ReplyDeleteCREATE 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'));
Nice article with simple example. Thank you!
ReplyDelete