-
Problem with Varray( Collection Types)
Hello,
I have written the following Package.
CREATE OR REPLACE PACKAGE BUSINESS_REFDATA.ESB_SERVICE1_UI AS
TYPE outputlist IS VARRAY(25) of varchar(10);
TYPE inputlist IS VARRAY(25) of varchar(10);
PROCEDURE PR_GET_LEADS1
(
P_BUS_CAT_CODE_SS IN inputlist,
OUT_DETAILS OUT outputlist
);
END ESB_SERVICE1_UI;
/
CREATE OR REPLACE PACKAGE BODY BUSINESS_REFDATA.ESB_SERVICE1_UI as
PROCEDURE PR_GET_LEADS1
(
P_BUS_CAT_CODE_SS IN inputlist,
OUT_DETAILS OUT outputlist
)
IS
i number;
v_count number;
BEGIN
for i in 1 .. P_BUS_CAT_CODE_SS.count
loop
SELECT COUNT(*) INTO V_COUNT FROM BUSINESS_CATEGORY
INNER JOIN LEADS ON BUSINESS_CATEGORY.BUS_CAT_ID = LEADS.BUS_CAT_ID
WHERE BUS_CAT_CODE_SS =P_BUS_CAT_CODE_SS(i) AND LEADS.BUS_CAT_ID < 2000000000 AND LEADS.BUS_CAT_ID > 1;
IF(V_COUNT>0) THEN
OUT_DETAILS(i):='Y';
ELSE
OUT_DETAILS(i):='N';
END IF;
END LOOP;
END;
END ESB_SERVICE1_UI;
/
and I am trying to execute the package as follows.
DECLARE
P_BUS_CAT_CODE_SS BUSINESS_REFDATA.ESB_SERVICE1_UI.INPUTLIST := BUSINESS_REFDATA.ESB_SERVICE1_UI.INPUTLIST(0000000019,0000000026,0000000037,0000000019);
out_details BUSINESS_REFDATA.ESB_SERVICE1_UI.outPUTLIST := BUSINESS_REFDATA.ESB_SERVICE1_UI.outputlist(10);
BEGIN
BUSINESS_REFDATA.ESB_SERVICE1_UI.PR_GET_LEADS1 (P_BUS_CAT_CODE_SS,out_details);
END;
/
I am getting a error called ora-06531 reference to uninitialized collection
I am not sure how to execute it other than this. I have initialises all the lists properly.
Please respond as it is urgent.
Keerthi
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|