DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: ORA-06533: Subscript beyond count

  1. #1
    Join Date
    Dec 2008
    Posts
    2

    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

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    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;

  3. #3
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,492

    Wink 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

  4. #4
    Join Date
    Dec 2008
    Posts
    2
    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
  •  


Click Here to Expand Forum to Full Width