1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Subscript beyond count - While using Nested Tables

Discussion in 'SQL PL/SQL' started by venu57, Nov 20, 2014.

  1. venu57

    venu57 Active Member

    Messages:
    23
    Likes Received:
    0
    Trophy Points:
    100
    Location:
    Bangalore
    Hi,

    In the below scenario, i'm trying to fetch the 100 to 107 employees data using for loop into a nested table. For that i have declared and initialized the nested table.
    Before fetching the data into the collection even i'm using collection_name.extend method to assign the null value. But i'm facing the below error.
    Please advice.


    DECLARE
    TYPE emp_rec_type IS TABLE OF employees%rowtype;
    emp_table emp_rec_type := emp_rec_type();

    BEGIN

    FOR i IN 100 .. 107
    LOOP
    emp_table.EXTEND();
    SELECT * INTO emp_table(i) FROM pgodamsetty.employees WHERE employee_id=i;
    END LOOP;
    END;
    /

    ERROR:

    ORA-06533: Subscript beyond count
    ORA-06512: at line 9
    06533. 00000 - "Subscript beyond count"
    *Cause: An in-limit subscript was greater than the count of a varray
    or too large for a nested table.
    *Action: Check the program logic and explicitly extend if necessary.
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Extending a table the way you have it coded extends it by one 'slot', and you want to start at 'slot' 100 which is impossible so you get this error:


    Code (SQL):

    SQL> DECLARE
      2   TYPE emp_rec_type IS TABLE OF employees%rowtype;
      3   emp_table emp_rec_type := emp_rec_type();
      4
      5   BEGIN
      6
      7   FOR i IN 100 .. 107
      8   LOOP
      9           emp_table.EXTEND();
     10           SELECT * INTO emp_table(i) FROM employees WHERE employee_id=i;
     11   END LOOP;
     12  END;
     13  /
    DECLARE
    *
    ERROR at line 1:
    ORA-06533: Subscript beyond COUNT
    ORA-06512: at line 10

    SQL>

     

    You need to extend the table by 100 first, then start processing your records, as the below example proves:


    Code (SQL):

    SQL> DECLARE
      2   TYPE emp_rec_type IS TABLE OF employees%rowtype;
      3   emp_table emp_rec_type := emp_rec_type();
      4
      5  BEGIN
      6
      7   emp_table.extend(100);
      8
      9   FOR i IN 100 .. 107
     10   LOOP
     11           SELECT * INTO emp_table(i) FROM employees WHERE employee_id=i;
     12           emp_table.EXTEND();
     13   END LOOP;
     14  END;
     15  /


    PL/SQL PROCEDURE successfully completed.


    SQL>
     
     
  3. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
  4. yowancristo

    yowancristo Forum Advisor

    Messages:
    103
    Likes Received:
    10
    Trophy Points:
    305
    Location:
    Bangalore
    Hi,

    As David mentioned, you have to extend 100 to use that slot.

    Defining a cursor and bulk collecting into table type will also do.


    Code (SQL):


    CURSOR   cur_emp   IS SELECT    * FROM EMPLOYEES;

     
    and then
    Code (SQL):


    OPEN cur_emp;

    FETCH cur_emp
    BULK COLLECT  INTO  emp_table;

    CLOSE cur_emp;
     
    Regards,
    Yowan Cristo
     
  5. venu57

    venu57 Active Member

    Messages:
    23
    Likes Received:
    0
    Trophy Points:
    100
    Location:
    Bangalore
    It's working David. Thank you very much.