PLS-00487 Invalid reference to variable

PLS-00487 Invalid reference to variable

Oracle SQL Error: PLS-00487 Invalid reference to variable

Cause:

A variable was referenced in a way that is inconsistent with its datatype.

Solution:

Check the spelling of the variable name. Make sure the variable was declared properly and that the declaration and reference are consistent regarding datatype.

Example:

DECLARE
	CURSOR c1 IS SELECT NAME FROM books;
	rec BOOKS.name%TYPE;
BEGIN
	OPEN c1;
	LOOP
	FETCH c1 INTO rec;
	EXIT WHEN c1%NOTFOUND;
	DBMS_OUTPUT.PUT_LINE(rec.NAME);
	END LOOP;
	CLOSE c1;
END;

Output:

ORA-06550: line 9, column 27:
PLS-00487: Invalid reference to variable ‘REC’

Correct

DECLARE
	CURSOR c1 IS SELECT NAME FROM books;
	rec BOOKS.name%TYPE;
BEGIN
	OPEN c1;
	LOOP
	FETCH c1 INTO rec;
	EXIT WHEN c1%NOTFOUND;
	DBMS_OUTPUT.PUT_LINE(rec);
	END LOOP;
	CLOSE c1;
END;

Output:

anonymous block completed