I have a table and it has 5 records I Want to create an VARRAY and fetch all firstName into The VARRAY using cursor

Question

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;/

i have an error:
ORA-06532: Subscript outside of limit ORA-06512: at line 14 06532. 00000 - "Subscript outside of limit"

pls help me

Answer 1

You should extend the varray.

Check the below 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();

FOR rec IN c_phonebook LOOP
i := i + 1;
v1.extend();
v1(i) := rec.FIRSTNAME;
END LOOP;

-- check varray values
FOR idx IN 1..v1.count LOOP
DBMS_OUTPUT.PUT_LINE (v1(idx));
END LOOP;
END;
/

Answer 2

thanks so much