0

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

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

4th Dec 2018, 5:41 PM
zahra
2 Respuestas
+ 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; /
5th Dec 2018, 7:06 AM
Prokopios Poulimenos
Prokopios Poulimenos - avatar
+ 1
thanks so much
5th Dec 2018, 8:55 AM
zahra