ORA-06533: Subscript beyond count

Scenario 1:
In  VARRAY, Once you initialized then you need to allocate space to store data by Using "EXTEND" method.

If you see below ,i commented Extend method so it's throwing error as "Subscript beyond count",It means it didn't find space/any empty index so we need to allocate Index using EXTEND method as follow.


 Running program with EXTEND method to correct this program.

SQL> set serveroutput on
SQL>
SQL> Declare
  2 
  3    Type Emp_Name is Varray(5) of Varchar2(100);
  4 
  5    V_Emp_Name Emp_Name := Emp_Name();
  6 
  7  Begin
  8    V_Emp_Name.Extend(5);
  9    V_Emp_Name(1) := 'Venky';
 10    V_Emp_Name(2) := 'Chinnu';
 11    V_Emp_Name(3) := 'Nikku';
 12    V_Emp_Name(4) := 'Ganesh';
 13    V_Emp_Name(5) := 'Devi';
 14 
 15    Dbms_Output.Put_line('5th Index Value Is ' || V_Emp_Name(5));
 16  End;
 17  /

5th Index Value Is Devi

PL/SQL procedure successfully completed

SQL> 

 Scenario 2:  When we use nested table.

We will check below same error for nested table .

As you observed above , its not throwing error at bulk collect even though we didn't allocate space as bulk collect will automatically initiate space and at the end we are assigning manually without allocating space so it will throw error.

Note that, we are deleting nested table so whatever space allocated to nested table will be deleted and if you omit that delete line of code (V_TY_TB_V_EMP.DELETE)  then we will not face issue even though if we assign manually to nested table up to whatever space is allocated through nested table.

eg:  If 10 records are stored using bulk collect  and then manually you can assign 10 records for same nested table, if you assign more than 10 then you will get same error.

Fix:

Using "V_TY_TB_V_EMP.EXTEND;" line of code, we can fix as its allocate space automatically until loop is over.

DECLARE

  TYPE TY_TB_V_EMP IS TABLE OF EMP%ROWTYPE;

  --TYPE TY_TB_V_EMP IS VARRAY(20) OF EMP%ROWTYPE;

  --  TYPE TY_TB_V_EMP IS TABLE OF EMP%ROWTYPE INDEX BY PLS_INTEGER;

  V_TY_TB_V_EMP TY_TB_V_EMP;

  IDX           BINARY_INTEGER := 0;

BEGIN

  SELECT * BULK COLLECT INTO V_TY_TB_V_EMP FROM EMP;

  DBMS_OUTPUT.PUT_LINE(' COUNT ' || V_TY_TB_V_EMP.COUNT);

  FOR K IN 1 .. V_TY_TB_V_EMP.COUNT LOOP

    DBMS_OUTPUT.PUT_LINE(' VALUES IS ' || V_TY_TB_V_EMP(K).empno);

  END LOOP;

  DBMS_OUTPUT.PUT_LINE(' COUNT ' || V_TY_TB_V_EMP.COUNT);

  V_TY_TB_V_EMP.DELETE;

  DBMS_OUTPUT.PUT_LINE(' COUNT ' || V_TY_TB_V_EMP.COUNT);

  FOR J IN (SELECT * FROM EMP) LOOP

    IDX := IDX + 1;

    V_TY_TB_V_EMP.EXTEND;

    V_TY_TB_V_EMP(IDX).ENAME := J.ENAME;

    DBMS_OUTPUT.PUT_LINE(' VALUES IS ' || V_TY_TB_V_EMP(IDX).ENAME);

  END LOOP;

END;

eg 2:


DECLARE
  TYPE TY_TB_V_PEOPLE_DET IS TABLE OF PEOPLE_DET%ROWTYPE;
  V_TY_TB_V_PEOPLE_DET TY_TB_V_PEOPLE_DET;
  l_error_count        NUMBER;
  IDX                  INTEGER := 0;
  CURSOR CUR_People_Det IS
    SELECT * FROM People_Det;
  bulk_dml_exp EXCEPTION;
  PRAGMA EXCEPTION_INIT(bulk_dml_exp, -24123);
BEGIN
--Initializing Nested table
V_TY_TB_V_PEOPLE_DET := TY_TB_V_PEOPLE_DET();
  FOR K IN CUR_People_Det LOOP
    IDX := IDX + 1;
    V_TY_TB_V_PEOPLE_DET(IDX) .PAN := K.PAN;
    V_TY_TB_V_PEOPLE_DET(IDX) .NAME := K.NAME;
  END LOOP;
DBMS_OUTPUT.PUT_LINE('Error Count ' || V_TY_TB_V_PEOPLE_DET.COUNT);
END;

ORA-06533: Subscript beyond count

ORA-06512: at line 16

Fix:

DECLARE

  TYPE TY_TB_V_PEOPLE_DET IS TABLE OF PEOPLE_DET%ROWTYPE;

  V_TY_TB_V_PEOPLE_DET TY_TB_V_PEOPLE_DET;

  l_error_count        NUMBER;

  IDX                  INTEGER := 0;

  CURSOR CUR_People_Det IS

    SELECT * FROM People_Det;

  bulk_dml_exp EXCEPTION;

  PRAGMA EXCEPTION_INIT(bulk_dml_exp, -24123);

BEGIN

--Initializing Nested table

V_TY_TB_V_PEOPLE_DET := TY_TB_V_PEOPLE_DET();

  FOR K IN CUR_People_Det LOOP

  --Allocating space Using Extend Method In loop so it will allocate until loop executes

  V_TY_TB_V_PEOPLE_DET.EXTEND;

    IDX := IDX + 1;

    V_TY_TB_V_PEOPLE_DET(IDX) .PAN := K.PAN;

    V_TY_TB_V_PEOPLE_DET(IDX) .NAME := K.NAME;

  END LOOP;

DBMS_OUTPUT.PUT_LINE(' Count ' || V_TY_TB_V_PEOPLE_DET.COUNT);

END;

Scenario 3: When we use Index by table.

We don't face this error when we are using index by table as it will take care space automatically while storing.