-
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;
-
Extend...
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 --
"The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb
-
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;
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|