Home » SQL & PL/SQL » SQL & PL/SQL » ORA-06533: Subscript beyond count (oracle,9i,XP)
ORA-06533: Subscript beyond count [message #375748] Sat, 13 December 2008 18:52 Go to next message
Anvesh REddy
Messages: 7
Registered: December 2008
Location: Charlotte,NC
Junior Member
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


[mod-edit: added code tags; next time please add them yourself]

[Updated on: Sun, 14 December 2008 01:04] by Moderator

Report message to a moderator

Re: ORA-06533: Subscript beyond count [message #375749 is a reply to message #375748] Sat, 13 December 2008 21:10 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7991
Registered: November 2002
Location: California, USA
Senior Member
You need to extend before you can assign a value:

v_CUMM_SALES_tp.EXTEND;

That is what causes the current error. It is saying that there is no element i.

You also need to assign a value to it:

v_CUMM_SALES_tp(i) := 0;

before you can add a value to it.

However, there are some other problems in your code. For example, you have an unnecessary loop. You don't need it with bulk collect. Please read the forum guide for how to post a complete question with create table and insert statements and results you want.

[Updated on: Sat, 13 December 2008 21:14]

Report message to a moderator

Re: ORA-06533: Subscript beyond count [message #375954 is a reply to message #375748] Mon, 15 December 2008 07:43 Go to previous messageGo to next message
Anvesh REddy
Messages: 7
Registered: December 2008
Location: Charlotte,NC
Junior Member
Hi Barbara

Thankyou for the reply.I have made changes to the code as suggested .But for some reason I still get the same error when I invoke a procedure from a PL/SQL block(Procedure compiles fine).Below are the changes to my 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;
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;

The error is as follows

ORA-06533: Subscript beyond count
ORA-06512: at "ANVESH.PROC_SALES", line 50
ORA-06512: at line 5

pointing to the following line in the code
v_CUMM_COMM_tp(i) := CALC_COMM(v_CUMM_SALES_tp(i));

the Pl/SQL block I have used to invoke the procedure is as follows


declare

begin

proc_sales;

end;


Re: ORA-06533: Subscript beyond count [message #375959 is a reply to message #375748] Mon, 15 December 2008 08:13 Go to previous messageGo to next message
Anvesh REddy
Messages: 7
Registered: December 2008
Location: Charlotte,NC
Junior Member
Finally got the solution.Here is the entire code with proper changes.

CREATE OR REPLACE procedure ANVESH.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) := 0;
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;


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;
/
Re: ORA-06533: Subscript beyond count [message #375967 is a reply to message #375959] Mon, 15 December 2008 08:48 Go to previous message
Michel Cadot
Messages: 59293
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for the feedback.

Before your next question, Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Previous Topic: substr prob
Next Topic: Which is better Option for Extracting the OBJECT DDL ?
Goto Forum:
  


Current Time: Thu Oct 02 03:05:03 CDT 2014

Total time taken to generate the page: 0.12318 seconds