ORA-06533: Subscript beyond count

In this forum you can share stuff related to Oracle 11g, 10g, 9i.
Post Reply
Anvesh reddy
Posts: 4
Joined: Sun Sep 21, 2008 2:56 pm
Location: USA

ORA-06533: Subscript beyond count

Post by Anvesh reddy »

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_date,'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
Post Reply

Who is online

Users browsing this forum: No registered users and 1 guest