Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to initialize and assign values to varray of Objects

629026Mar 18 2008 — edited Mar 19 2008
I 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
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 16 2008
Added on Mar 18 2008
2 comments
655 views