This content has been marked as final. Show 3 replies
The cursor that you are opening selects the EMPNO, EMPNM, and DEPTNO in that order.
When you fetch from the cursor, you are fetching into L_EMPNM, L_EMPNO, L_DEPTNO.
It would appear that you have reversed the first two columns in your FETCH statement. Assuming that EMPNO is a NUMBER and EMPNM is a VARCHAR2, that means that Oracle is trying to implicitly cast the EMPNM into a NUMBER which causes the error.
If you change the order of the variables in your FETCH statement, the error should disappear
FETCH L_CURSOR INTO L_EMPNO, L_EMPNM, L_DEPTNO;
Your fetch into columns are in the wrong order. They must be in the same order as the select in the ref cursor. Your second column in the fetch into list is empno, but the second column in the select is empnm, so your trying to fetch the name into a number column. That's why you're getting the error.
Your SELECT column list is EMPNO, EMPNM, DEPTNO.
Your FETCH column list is L_EMPNM, L_EMPNO, L_DEPTNO.
So your code implicitly converts EMPNO into a string for L_EMPNM.
And then it implicitly tries to convert EMPNM into a number for L_EMPNM.
And that fails with an invalid number exception ;-)
(PS. Please cut and paste the error next time. Not everybody remembers all the error codes in their head...)