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

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 ---

---

;)

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.

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.
:p