ORA-06531: Reference to uninitialized collection

[gnagesh]

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

[liyakat]

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

[satvelucbe]

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

[gnagesh]

Thanks to all.

Regards