Question

Please help with the following code. I keep getting error ORA-06533: Subscript beyond count. I am trying to get the first 10 records in the emp table listed in reverse. I also get error ORA-6512. Please help.

DECLARE
CURSOR e_emp is
SELECT ename, sal FROM emp;
type namesarray IS VARRAY(10) OF emp.ename%type;
type salarray IS VARRAY(10) OF emp.sal%type;
names namesarray := namesarray();
salary salarray := salarray();
counter integer := 10;
BEGIN
FOR n IN REVERSE e_emp LOOP
counter := counter - 1;
names.extend;
salary.extend;
names(counter) := n.ename;
salary(counter) := n.sal;
dbms_output.put_line('Emp Name: '||names(counter)||', Salary:'|| salary(counter));
END LOOP;
END;

/

This is what I get when I run it in SQLPLUS:

SQL> DECLARE
2 CURSOR e_emp is
3 SELECT ename, sal FROM emp;
4 type namesarray IS VARRAY(11) OF emp.ename%type;
5 type salarray IS VARRAY(11) OF emp.sal%type;
6 names namesarray := namesarray();
7 salary salarray := salarray();
8 counter integer := 10;
9 BEGIN
10 FOR n IN REVERSE e_emp LOOP
11 counter := counter - 1;
12 names.extend;
13 salary.extend;
14 names(counter) := n.ename;
15 salary(counter) := n.sal;
16 dbms_output.put_line('Emp Name: '||names(counter)||', Salary:'|| salary(counter));
17 END LOOP;
18 END;
19 /
DECLARE
*
ERROR at line 1:
ORA-06533: Subscript beyond count
ORA-06512: at line 14

Answer 1

SQL> DECLARE
2 CURSOR e_emp is
3 SELECT ename, sal FROM emp;
4 type namesarray IS VARRAY(11) OF emp.ename%type;
5 type salarray IS VARRAY(11) OF emp.sal%type;
6 names namesarray := namesarray();
7 salary salarray := salarray();
8 counter integer := 10;
9 BEGIN
10 FOR n IN REVERSE e_emp LOOP
11 counter := counter - 1;
12 names.extend;
13 salary.extend;
14 names(counter) := n.ename;
15 salary(counter) := n.sal;
16 dbms_output.put_line('Emp Name: '||names(counter)||', Salary:'|| salary(counter));
17 END LOOP;
18 END;
19 /

At line no 6,7 array has been initializes. and in reversed loop is is extending one by one and adding value the array.

but counter starts from 10, initially name will have size 1. but here code is trying to use names(counter) := n.ename;

name(10):=n.ename

thats why that error is coming.

Solution.

first try to add name sal to respective array in ascending order only. then reverse all the array.