Error - ORA-06502: PL/SQL: numeric or value error

Question

I am learning PL/SQL. I have written the procedure below using cursor and nested table to display employee names.

create or replace procedure
  employees_data
is
  cursor c1 is select * from employees;
  type empl_tbl is table of c1%rowtype;
  emp_data empl_tbl;
begin
  open c1; 
    LOOP
    fetch c1 bulk collect into emp_data limit 100;
    exit when sql%notfound;
    for i in 1..emp_data.last
      loop
      dbms_output.put_line ('employee name is : ' || to_char(emp_data(i).first_name));
    end loop;
    end loop;
    close c1;
end employees_data;

It compilesd without any errors. When I am executing the procedure, I am able to display all the employees names. But, the below error is thrown before displaying data. Can anyone please help me out of this?

Error starting at line : 1 in command -
exec employees_data()
Error report -
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "HR.EMPLOYEES_DATA", line 12
ORA-06512: at line 1
06502. 00000 -  "PL/SQL: numeric or value error%s"
*Cause:    An arithmetic, numeric, string, conversion, or constraint error
           occurred. For example, this error occurs if an attempt is made to
           assign the value NULL to a variable declared NOT NULL, or if an
           attempt is made to assign an integer larger than 99 to a variable
           declared NUMBER(2).
*Action:   Change the data, how it is manipulated, or how it is declared so
           that values do not violate constraints.

Thanks in advance.

Answer 1

Your output suggests you have not enabled DBMS_OUTPUT. With set serveroutput on it becomes obvious that the error is thrown once all the employees have been processed (I added a row count to the display):

#100:employee name is : Douglas
#1:employee name is : Jennifer
#2:employee name is : Michael
#3:employee name is : Pat
#4:employee name is : Susan
#5:employee name is : Hermann
#6:employee name is : Shelley
#7:employee name is : William
BEGIN employees_data; END;

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "HR.EMPLOYEES_DATA", line 12
ORA-06512: at line 1


SQL> 

So, why is this happening? It's because you're using the wrong test after the FETCH. SQL%NOTFOUND is a test for SQL statements embedded in PL/SQL. A FETCH from an Explicit Cursor is not a SQL operation.

SQL%NOTFOUND is never true after a FETCH, which means the EXIT WHEN condition is never met. Hence, the program continues to loop after all the records have been fetched. The program hurled ORA-06502 because emp_data.last is null after all the records have been fetched, so the LOOP test blows up.

The best solution is to test for the number of rows returned into the array:

fetch c1 bulk collect into emp_data limit 100;
exit when emp_data.count()=0;

With this one change your procedure will run:

#100:employee name is : Douglas
#1:employee name is : Jennifer
#2:employee name is : Michael
#3:employee name is : Pat
#4:employee name is : Susan
#5:employee name is : Hermann
#6:employee name is : Shelley
#7:employee name is : William

PL/SQL procedure successfully completed.

SQL>

Note that you should not use exit when c1%notfound; . Although that is correct syntax for testing whether an Explicit Cursor returns a result, its behaviour is different (and non-intuitive) with bulk operations. Then, the test is only true when the FETCH returns the exact number of rows specified in the LIMIT clause. In your scenario it means you lose the last seven records:

#98employee name is : Kevin
#99employee name is : Donald
#100employee name is : Douglas

PL/SQL procedure successfully completed.

SQL>

---

Incidentally, casting FIRST_DATE is unnecessary because (assuming you are using the standard HR schema) it's already a string. We need to use to_char() for things like numbers or dates, so we can control the formatting.