Oracle – ORA 06533: Subscript beyond count

oracleplsql

I created below simple block but getting

ORA 06533:Subscript beyond count

error.

Can someone please tell me what I am missing in below code.

declare
  type salaryvarray is varray(6) of customers.salary%type;
  salary_array salaryvarray:=salaryvarray();
  c_salary customers.salary%type;
  i integer(2);
  counter number(2);
begin
  salary_array.extend;
  select count(*) into counter from customers;
  for i in 1..counter loop
    select salary into c_salary from customers where id =i;
    salary_array(i):=c_salary;
  end loop;
end;
/

Best Solution

The array_var.extend portion of the code needs to be inside the loop. Each time you add to it, you are allocating new memory. Skipping this step is asking the code to store something without giving it space.

declare
  type salaryvarray is varray(6) of customers.salary%type;
  salary_array salaryvarray:=salaryvarray();
  c_salary customers.salary%type;
  i integer(2);
  counter number(2);
begin
  select count(*) into counter from customers;
  for i in 1..counter loop
    salary_array.extend; -- Extend for each value.
    select salary into c_salary from customers where id =i;
    salary_array(i):=c_salary;
  end loop;
end;
/

You will very likely run into a similar error soon, however, ORA-06532: Subscript outside of limit. You limit your VARRAY to 6 elements, but customers could potential have more. Consider limiting the return, expanding the VARRAY or implementing a more dynamic collection type.

Related Solutions

Oracle – Unable to add data to Oracle Custom Record – Error – Subscript beyond count

Several issues:

You are not extending your collection (despite that the .extend() method is there ... commented out).
You are not instantiating your object class learnerMapCustomRecord.
You are not closing your cursor c1.
Your output parameter has the same name as your class, effectively making instantiation of your object class more difficult because of default identifier scope resolution.
You are making too many roundtrips to the SQL engine (and too much work for yourself as well) by using row-by-row fetch where you can do a regular cursor for-loop or a bulk fetch.

Solution:

create or replace PROCEDURE getLearnerMapDetails(theOutput out learnerMapCustomRecordTable)
as
  cursor c1 is select object_name,status from user_objects  where rownum <= 2;
  c c1%rowtype;
begin
  theOutput := learnerMapCustomRecordTable();
  open c1;
  loop
    fetch c1 into c;
    EXIT WHEN C1%NOTFOUND;
    dbms_output.put_line(c.object_name||'==>'||c.status);
    theOutput.extend();
    theOutput(theOutput.last) := new learnerMapCustomRecord(
        activityName => c.object_name,
        activityDescn => c.status
    );
  end loop; 
  close c1;
end;

... and with the SQL engnie roundtrips reduction ...

create or replace PROCEDURE getLearnerMapDetails(theOutput out learnerMapCustomRecordTable)
as
  cursor c1 is select object_name,status from user_objects  where rownum <= 2;
begin
  theOutput := learnerMapCustomRecordTable();
  for c in c1 loop
    dbms_output.put_line(c.object_name||'==>'||c.status);
    theOutput.extend();
    theOutput(theOutput.last) := new learnerMapCustomRecord(
        activityName => c.object_name,
        activityDescn => c.status
    );
  end loop; 
end;

... and if you want to do it a "tough guy way", then you could do it this way ...

create or replace PROCEDURE getLearnerMapDetails(theOutput out learnerMapCustomRecordTable) as
begin
    select new learnerMapCustomRecord(
        activityName => object_name,
        activityDescn => status
    )
    bulk collect into theOutput
    from user_objects
    where rownum <= 2;
end;

Enjoy.

Sql – oracle – ORA 06533 subscript beyond count

Change how you are iterating through your collection...

Rather than using

    FOR i IN v_t_report.FIRST .. v_t_report.LAST

try

    FOR i IN 1.. v_t_report.COUNT

example:

    PROCEDURE Put_Records_p
    IS
      TYPE REPORT_TABLE_TYPE IS REF CURSOR
         RETURN TYP_REPORT_REC;
       cur_claim_report          REPORT_TABLE_TYPE;
       v_t_report                TYP_REPORT_TAB;
       v_text_line_temp          VARCHAR2 (32767);

              BEGIN
                 SELECT TO_DATE (variable_value, 'mm/dd/yyyy')
                   INTO g_effective_date
                   FROM SYSTEM_CONFIG
                  WHERE VARIABLE_NAME = 'EFFECTIVE_DATE';
              EXCEPTION
                 WHEN NO_DATA_FOUND
                 THEN
                    g_effective_date := TO_DATE ('04/01/2015', 'MM/DD/YYYY');
              END;

         cur_claim_report := open_report_cur_f;

         LOOP
            FETCH cur_claim_report
            BULK COLLECT INTO v_t_report
            LIMIT G_BULK_LIMIT;

            FOR i IN 1..v_t_report.COUNT
            LOOP
              IF v_t_report (i).CM_DOI < g_effective_date
              THEN
                 v_text_line_temp := v_text_line_temp || RPAD (' ', 1);
              ELSIF v_t_report (i).CM_DOI >= g_effective_date
              THEN
    --Line number pointed to below portion. I'm not sure what needs to be added or modified to avoid having the error.
                 v_text_line_temp :=
                       v_text_line_temp
                    || RPAD(NVL(v_t_report (i).report_ic_tab (1).IC_VERSION, 
                                '0'),
                            1,
                            '0');
              END IF;
            END LOOP;

            EXIT WHEN cur_cl_report%NOTFOUND;
         END LOOP;
    END;

Best Solution

Related Solutions

Oracle – Unable to add data to Oracle Custom Record – Error – Subscript beyond count

Sql – oracle – ORA 06533 subscript beyond count

Related Question