Problem with Varray( Collection Types)
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Problem with Varray( Collection Types)

  1. #1
    Join Date
    Jan 2010
    Posts
    2

    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

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,441

    Cool 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

  3. #3
    Join Date
    Jan 2010
    Posts
    2

    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.

  4. #4
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,441

    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
  •  



Click Here to Expand Forum to Full Width