How to initialize and assign values to varray of Objects
629026Mar 18 2008 — edited Mar 19 2008I have created a oracle object
create type dedupetype as object
(applid number(30), productid varchar2(40));
create type dedupetypearray is varray(100) of dedupetype;
Now in the pl/sql procedure i want to initialize the dedupetypearray and assign the values that i get from cursor to dedupetypearay. My code is
create or replace PROCEDURE DEDUPE_GET_APPID_SP(custid in number,darray out nocopy DEDUPETYPEARRAY) AS
cursor c1 is select appl_id,product_id from ids where cust_id = custid;
v_applid varchar2(50);
v_productid varchar2(50);
v_count number;
i number := 1;
v_dedupetype dedupetype := dedupetype(null,null);
v_darray dedupetypearray;
BEGIN
select count(*) into v_count from ids where cust_id=custid;
v_darray.extend(v_count);
open c1;
loop
fetch c1 into v_applid,v_productid;
exit when c1%notfound;
v_dedupetype := dedupetype(v_applid,v_productid);
v_darray(i) := v_dedupetype;
i := i+1;
end loop;
close c1;
darray := v_darray;
END DEDUPE_GET_APPID_SP;
when i run the procedure i get
ORA-06531: Reference to uninitialized collection
ORA-06512: at "CPCSUSER.DEDUPE_GET_APPID_SP", line 12
ORA-06512: at line 7
It says that the object in v_darray has not been initialized. How do u initialize the object in an array and how to assign the values to the object in an array.
Kindly help me