FETCHing Records From A Cursor

[arpan]

What's wrong with the following code (providing the line numbers too)?

1. DECLARE
2. CURSOR c1 IS SELECT EName FROM Emp;
3. name1 Emp.EName%TYPE;
4. name2 Emp.EName%TYPE;
5. name3 Emp.EName%TYPE;
6. BEGIN
7. OPEN c1;
8. LOOP
9. FETCH c1 INTO name1; -- Fetch the first row
10. FETCH c1 INTO name2; -- Fetch the second row
11. FETCH c1 INTO name3; -- Fetch the third row
12. DBMS_OUTPUT.PUT_LINE('NAME1 : ' || name1.EName);
13. DBMS_OUTPUT.PUT_LINE('NAME2 : ' || name2.EName);
14. DBMS_OUTPUT.PUT_LINE('NAME3 : ' || name3.EName);
15. END LOOP;
16. CLOSE c1;
17. END;
18. /

The above code when executed displays the following errors:

PLS-00487: Invalid reference to variable 'NAME1'
PLS-00487: Invalid reference to variable 'NAME2'
PLS-00487: Invalid reference to variable 'NAME3'

which points to line 12, 13 & 14 respectively. What's the problem?

Thanks,

Arpan

 

[jeppa1]

The problem to your specific problem is at the end of the lines 12,13, and 14. You do not need the .EName after the call to the cursor record. Then you have the problem of a never ending loop.

Try the following implicit cursor:
begin
for rec in (select EName from EMP)
loop
DBMS_OUTPUT.PUT_LINE('NAME1 : ' ||rec.EName);
end loop;
end;

If you still want to use your explicit cursor you will have to do a record count somewhere in the statement to let the loop know when to end.

 

[nme]

Isn't it just a case of you incorrectly referencing the variable, you have used it as if you are bringing back a record. Therefore
12. DBMS_OUTPUT.PUT_LINE('NAME1 : ' || name1.EName);
should actually be
12. DBMS_OUTPUT.PUT_LINE('NAME1 : ' || name1);

shouldn't it?!?