ORA-06533: Subscript beyond count

Dear Members

I am getting the following error while working toad.I am using oracle 9i

ORA-06533: Subscript beyond count
ORA-06512: at line 42
ORA-06512: at line 5

pointing to the following line in the code

v_CUMM_SALES_tp(i) := v_CUMM_SALES_tp(i) + v_l_count_tp(i);

I am not sure what is wrong.It will be great help if some can correct the error

CREATE OR REPLACE procedure proc_sales is

TYPE sales_rep_id_tp IS TABLE OF NUMBER;
v_sales_rep_id_tp sales_rep_id_tp := sales_rep_id_tp();
TYPE first_name_tp IS TABLE OF VARCHAR2(30);
v_first_name_tp first_name_tp := first_name_tp();
TYPE last_name_tp IS TABLE OF VARCHAR2(30);
v_last_name_tp last_name_tp := last_name_tp();

TYPE l_count_tp IS TABLE OF NUMBER;
v_l_count_tp l_count_tp := l_count_tp();
TYPE l_month_tp IS TABLE OF VARCHAR2(30);
v_l_month_tp l_month_tp := l_month_tp();
TYPE l_year_tp IS TABLE OF VARCHAR2(30);
v_l_year_tp l_year_tp := l_year_tp();

TYPE CUMM_SALES_tp IS TABLE OF NUMBER;
v_CUMM_SALES_tp CUMM_SALES_tp := CUMM_SALES_tp();

TYPE CUMM_COMM_tp IS TABLE OF NUMBER;
v_CUMM_COMM_tp CUMM_COMM_tp := CUMM_COMM_tp();

CURSOR C1 IS
select a.sales_rep_id,a.first_name,a.last_name,count(product_id),to_char(sold_date,'month'),to_char(sold_da te,'yyyy')
from sales_reps a,sales_info b
where active_flag='Y'
and a.SALES_REP_ID=b.SALES_REP_ID
group by a.sales_rep_id,a.first_name,a.last_name,to_char(b.sold_date,'month'),to_char(b.sold_date,'yyyy');

begin

OPEN C1;

LOOP

FETCH C1 bulk collect into v_sales_rep_id_tp,v_first_name_tp,v_last_name_tp,v_l_count_tp,v_l_month_tp,v_l_year_tp;

FOR i IN v_first_name_tp.FIRST .. v_first_name_tp.LAST
LOOP
v_CUMM_SALES_tp(i) := v_CUMM_SALES_tp(i) + v_l_count_tp(i);
v_CUMM_COMM_tp(i) := CALC_COMM(v_CUMM_SALES_tp(i));
END LOOP;

IF (C1%NOTFOUND)
THEN
EXIT;
END IF;

END LOOP;

for i in v_first_name_tp.first .. v_first_name_tp.last loop

dbms_output.put_line('Sales Rep ID is ' || v_sales_rep_id_tp(i));
dbms_output.put_line('First Name is ' || v_first_name_tp(i));
dbms_output.put_line('Last Name is ' || v_last_name_tp(i));
dbms_output.put_line('Monthly Sales ' || v_l_count_tp(i));
dbms_output.put_line('Month is ' || v_l_month_tp(i));
dbms_output.put_line('Year is ' || v_l_year_tp(i));

end loop;

FORALL i IN v_first_name_tp.FIRST .. v_first_name_tp.LAST
insert into monthly_sales_info values (v_first_name_tp(i),v_last_name_tp(i),v_l_month_tp(i),v_l_year_tp(i),v_l_count_tp(i),v_CUMM_SALES_tp (i),v_CUMM_COMM_tp(i));
COMMIT;

CLOSE C1;

end;

Thanks
Anvesh

That would make sense. Where do you populate v_CUMM_SALES_tp with data?

Code:

---

FETCH C1 bulk collect into v_sales_rep_id_tp,v_first_name_tp,v_last_name_tp,v_l_count_tp,v_l_month_tp,v_l_year_tp;

FOR i IN v_first_name_tp.FIRST .. v_first_name_tp.LAST
LOOP
v_CUMM_SALES_tp(i) := v_CUMM_SALES_tp(i) + v_l_count_tp(i);
v_CUMM_COMM_tp(i) := CALC_COMM(v_CUMM_SALES_tp(i));
END LOOP;

---

Try this:

Code:

---

CREATE OR REPLACE procedure proc_sales is
-- Etc --
BEGIN
-- Etc --
FOR i IN v_first_name_tp.FIRST .. v_first_name_tp.LAST
LOOP
  IF NOT v_CUMM_SALES_tp.EXISTS(i) THEN
    v_CUMM_SALES_tp.EXTEND; v_CUMM_SALES_tp(i):=0;
  END IF;
  v_CUMM_SALES_tp(i) := v_CUMM_SALES_tp(i) + v_l_count_tp(i);
  v_CUMM_COMM_tp(i) := CALC_COMM(v_CUMM_SALES_tp(i));
END LOOP;
-- Etc --

---

:p

thankyou for the prompt reply.got the desired output.here is the modified code.

Code:

FOR i IN v_first_name_tp.FIRST .. v_first_name_tp.LAST
LOOP
v_CUMM_SALES_tp:= CUMM_SALES_tp();
v_CUMM_SALES_tp.EXTEND(i);
v_CUMM_SALES_tp(i) := v_CUMM_SALES_tp(i) + v_l_count_tp(i);
v_CUMM_COMM_tp := CUMM_COMM_tp();
v_CUMM_COMM_tp.EXTEND(i);
v_CUMM_COMM_tp(i) := CALC_COMM(v_CUMM_SALES_tp(i));
END LOOP;