Fetching Table Data with Cursor to a Varray in oracle database

Question

I have a table called PhoneBook with this attributes: ID, FirstName, LastName. and it has 5 records I Want to create an VARRAY and fetch all firstNames in the PhoneBook Table into the VARRAY using Cursor. Here is my code:

DECLARE
    v_FirstName PHONEBOOK.FIRSTNAME%TYPE;
    TYPE arrayNames IS VARRAY(10) OF VARCHAR2(20);
    v1 arrayNames;
    i INT := 0;
    CURSOR c_phonebook IS SELECT FIRSTNAME FROM PHONEBOOK;
       BEGIN
           v1 := arrayNames(); 
           OPEN c_phonebook;
           LOOP
                FETCH c_phonebook INTO v_firstname;
                v1(i) := v_firstname;
                i := i+1;
                EXIT WHEN c_phonebook%NOTFOUND;
           END LOOP;

          CLOSE c_phonebook;

       END;/

When I Compile this code it gave me an error:

ORA-06532: Subscript outside of limit ORA-06512: at line 14 06532. 00000 - "Subscript outside of limit" *Cause: A subscript was greater than the limit of a varray or non-positive for a varray or nested table. *Action: Check the program logic and increase the varray limit if necessary.

Please help me to solve this problem. thank you

Answer 1

You need to increase the size of the array, creating an empty element you can then populate, each time you want to add a member:

...
BEGIN
    v1 := arrayNames();
    OPEN c_phonebook;
    LOOP
        FETCH c_phonebook INTO v_firstname;
        EXIT WHEN c_phonebook%NOTFOUND;
        i := i+1;
        v1.EXTEND();
        v1(i) := v_firstname;
    END LOOP;
    CLOSE c_phonebook;
END;

Notice that I've moved the i := i+1 to before the assignment, as the array is indexed from 1 rather than 0. I've also moved the EXIT to immediately after the FETCH, so you don't try to process the last row from the cursor twice.

You don't actually need i in this example, you can use the array's current COUNT to identify the newly-added entry:

DECLARE
    v_FirstName PHONEBOOK.FIRSTNAME%TYPE;
    TYPE arrayNames IS VARRAY(10) OF VARCHAR2(20);
    v1 arrayNames;
    CURSOR c_phonebook IS SELECT FIRSTNAME FROM PHONEBOOK;
BEGIN
    v1 := arrayNames(); 
    OPEN c_phonebook;
    LOOP
        FETCH c_phonebook INTO v_firstname;
        EXIT WHEN c_phonebook%NOTFOUND;
        v1.EXTEND();
        v1(v1.COUNT) := v_firstname;
    END LOOP;
    CLOSE c_phonebook;
END;
/

---

I assume this is an exercise; otherwise you could use a bulk collect into the collection, and don't need an explicit cursor:

DECLARE
    TYPE arrayNames IS VARRAY(10) OF VARCHAR2(20);
    v1 arrayNames;
BEGIN
    SELECT FIRSTNAME
    BULK COLLECT INTO v1
    FROM PHONEBOOK;
END;
/

(You could add a rownum check to the query to make sure it doesn't try to get more rows that the array can handle, but it might be more appropriate for that to error if it ever happens.)