Error while compiling cursor in plsql block

Question

I have a table as employee. I am new to oracle.I am creating the cursor but when I compile,I get error:

DECLARE 
    CURSOR c_data IS 
    SELECT distinct dept_id 
      FROM offc.employee; 

    tmp_event offc.employee.dept_id%type; 

BEGIN 
    OPEN c_data; 

    LOOP 
        FETCH c_data INTO tmp_event; 
      EXIT WHEN c_data%NOTFOUND; 

        Dbms_Output.Put_Line(tmp_event.dept_id); 
    END LOOP; 

    CLOSE c_data; 
END;     
/ 

I got the error as follows:

Error at line 1 ORA-06550: line 15, column 40: PLS-00487: Invalid reference to variable 'TMP_EVENT' ORA-06550: line 15, column 9: PL/SQL: Statement ignored

I think there is problem in tmp_event declaration.How to handle this error?

Answer 1

You should use Dbms_Output.Put_Line(tmp_event);

where tmp_event is a variable which is already of type offc.employee.dept_id%type

This link would help for details.

Answer 2

Try Below Query

DECLARE 
        CURSOR c_data IS 
          SELECT distinct dept_id 
          FROM   offc.employee; 
        tmp_event offc.employee.dept_id%type; 

    BEGIN 
        OPEN c_data; 

        LOOP 
            FETCH c_data INTO tmp_event; 

            EXIT WHEN c_data%NOTFOUND; 

            dbms_output.Put_line(tmp_event); --Dont Use Dept id
        END LOOP; 

        CLOSE c_data; 
    END; 

    / 

Answer 3

The following PL/SQL statement is equivalent to the original statement, but way shorter and thus less prone to programming errors.

DECLARE
  CURSOR c_data IS
    SELECT DISTINCT dept_id 
      FROM offc.employee;

BEGIN
  FOR r_data IN c_data LOOP
    Dbms_Output.Put_Line(r_data.dept_id);
  END LOOP;
END;
/