ORA-06531: Reference to uninitialized collection
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: ORA-06531: Reference to uninitialized collection

  1. #1
    Join Date
    Aug 2000
    Location
    Singapore
    Posts
    323
    Hi

    I have the following PL/SQL procedure code, in which first time I am using VARRAY's, While executing this procedure I am getting ORA-06531:Reference to unintialized collection, Please help me to resolve this problem. Here is the code:

    CREATE OR REPLACE PROCEDURE get_disc_percent(p_acc_usage IN NUMBER,
    p_discount OUT NUMBER)
    AS

    CURSOR C1 IS SELECT PROMO_TYPE,TRIGGER_COUNT,DISCOUNT,ADD_AMOUNT
    FROM PROMOTION_ITEM
    WHERE PROMO_ITEM_ID > 0
    AND TRIGGER_TYPE ='C'
    AND START_PROMO <= TRUNC(SYSDATE)
    AND END_PROMO >= TRUNC(SYSDATE)
    ORDER BY PROMO_TYPE,TRIGGER_COUNT;

    v_promo_type PROMOTION_ITEM.PROMO_TYPE%TYPE;
    v_trigger_count PROMOTION_ITEM.TRIGGER_COUNT%TYPE;
    v_discount PROMOTION_ITEM.DISCOUNT%TYPE;
    v_add_amount PROMOTION_ITEM.ADD_AMOUNT%TYPE;
    v_min_range PLS_INTEGER;
    v_max_range PLS_INTEGER;
    v_min_discount PROMOTION_ITEM.DISCOUNT%TYPE;
    v_max_discount PROMOTION_ITEM.DISCOUNT%TYPE;

    TYPE va_promo_trig IS VARRAY(1000) OF NUMBER;
    TYPE va_promo_disc IS VARRAY(1000) OF NUMBER;

    i PLS_INTEGER;

    v_promo_trig va_promo_trig;
    v_promo_disc va_promo_disc;

    BEGIN
    BEGIN
    v_min_range := 0;
    v_max_range := 0;
    SELECT min(trigger_count), max(trigger_count)
    INTO v_min_range, v_max_range
    FROM PROMOTION_ITEM
    WHERE PROMO_ITEM_ID > 0
    AND TRIGGER_TYPE ='C'
    AND PROMO_TYPE = 'D'
    AND START_PROMO <= TRUNC(SYSDATE)
    AND END_PROMO >= TRUNC(SYSDATE);
    END;

    BEGIN
    v_min_discount := 0;
    v_max_discount := 0;
    SELECT min(discount), max(discount)
    INTO v_min_discount, v_max_discount
    FROM PROMOTION_ITEM
    WHERE PROMO_ITEM_ID > 0
    AND TRIGGER_TYPE ='C'
    AND PROMO_TYPE = 'D'
    AND START_PROMO <= TRUNC(SYSDATE)
    AND END_PROMO >= TRUNC(SYSDATE);
    END;


    IF(v_min_range = 0 AND v_max_range = 0) THEN
    p_discount:= 0;
    RETURN;
    END IF;

    IF(p_acc_usage < v_min_range) THEN
    p_discount := 0;
    RETURN;
    END IF;

    IF(p_acc_usage > v_max_range) THEN
    p_discount := v_max_discount;
    RETURN;
    END IF;

    OPEN C1;
    LOOP
    FETCH C1 INTO v_promo_type,v_trigger_count,v_discount,
    v_add_amount;

    EXIT WHEN C1%NOTFOUND;

    v_promo_trig(i) := v_trigger_count;
    v_promo_disc(i) := v_discount;
    i := i + 1;
    END LOOP;
    CLOSE C1;
    END GET_DISC_PERCENT;
    /

    Thanks In Advance


    Nagesh

  2. #2
    Join Date
    Sep 2002
    Posts
    5

    ORA-06531: Reference to uninitialized collection

    hello

    u have no initialized the variable i, and as its null the assignment won't work at the bottom part of the code.

    regards

    Liyakat

  3. #3
    Join Date
    Jun 2002
    Posts
    7
    Hi,
    The varray should be initialized with lower and upper bound.

    v_promo_trig:=va_promo_trig(0,999);
    v_promo_disc:=va_promo_disc(0,999);



    With regards,
    V.Sathishkumar
    Your efforts may fail. But don't fail to make any efforts

  4. #4
    Join Date
    Aug 2000
    Location
    Singapore
    Posts
    323
    Thanks to all.

    Regards
    Nagesh

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