-
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
-
Declare the arrays
You need to declare the arrays:
Code:
CREATE OR REPLACE PACKAGE BUSINESS_REFDATA.ESB_SERVICE1_UI AS
TYPE list_array IS VARRAY(25) of varchar(10);
outputlist list_array;
inputlist list_array;
PROCEDURE PR_GET_LEADS1
-- Etc ---
"The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb
-
Declaration
Hello, I have declared the inputlist and outputlist arrays as the type of the package
So it is not because of the declaration and I have initialised it as well.
-
IN or OUT? that is the question...
Set the out list to IN OUT:
Code:
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 IN 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 IN 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;
/
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;
/
And next time please format your code.
"The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb
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
|