I am getting the error Subscript outside of limit. How do I fix it?

Question

I know that it is the line of code that says empName(COUNTER) := ENAME; but I cant seem to figure out what the problem is.

SET SERVEROUTPUT ON;

DECLARE
    TYPE nameList IS VARRAY (10) OF VARCHAR2(10);
    empName nameList := nameList();
    ENAME    VARCHAR2(10);
    SAL      NUMBER(7,2);
    COUNTER  NUMBER := 0;

    CURSOR C1 IS
        SELECT ENAME, SAL
        FROM EMP
        WHERE ROWNUM < 11;        
BEGIN
    FOR person IN C1
    LOOP
        ENAME := person.ENAME;
        SAL := person.SAL;
        empName(COUNTER) := ENAME;
        COUNTER := COUNTER + 1;
    END LOOP;
END;
/

score 2 · Answer 1 · answered Jun 22 '21 at 21:23

Two errors:

varray indexing starts at 1, not 0 (line #6)
you forgot to extend it (line #14)

SQL>   DECLARE
  2      TYPE nameList IS VARRAY (20) OF VARCHAR2(10);
  3      empName nameList := nameList();
  4      ENAME    VARCHAR2(10);
  5      SAL      NUMBER(7,2);
  6      COUNTER  NUMBER := 1;             --> not 0 but 1
  7      CURSOR   C1 IS SELECT ENAME, SAL
  8               FROM EMP
  9               WHERE ROWNUM < 11;
 10
 11    BEGIN
 12          FOR person IN C1
 13          LOOP
 14             empName.extend;             --> you're missing this
 15             ENAME := person.ENAME;
 16             SAL := person.SAL;
 17             empName(COUNTER) := ENAME;
 18             COUNTER := COUNTER + 1;
 19          END LOOP;
 20    END;
 21    /

PL/SQL procedure successfully completed.

SQL>

I assumed extend makes the varray bigger. is that wrong? Curious so I understand how its implemented. — Jeremy Hahn, Jun 22 '21 at 21:42
Yes, extend makes the varray bigger. It starts out empty. `VARRAY(20)` means it is limited to 20 elements. I recommend using a normal nested table collection type with more functionality and no limit. — William Robertson, Jun 22 '21 at 22:15

I am getting the error Subscript outside of limit. How do I fix it?

1 Answers1