using cursor attributes in a CURSOR FOR LOOP

Question

I am running the following in the Scott schema:

SET serveroutput ON;

BEGIN
FOR c_Emp IN (SELECT * FROM emp)
LOOP
dbms_output.put_line('The record processed by the cursor ' || c_Emp%rowcount);
END LOOP;
end;

This gives the error:

cursor attribute may not be applied to non-cursor 'C_EMP'

However if this is done using an explicit cursor it works fine:

set serveroutput on ;

DECLARE 
       emp_record emp%ROWTYPE; 
       count_variable NUMBER;
       CURSOR c IS 
SELECT * FROM emp;
BEGIN
OPEN c;
loop
fetch  c INTO emp_record;
exit WHEN c%notfound;
dbms_output.put_line ('The record count is   ' || c%rowcount);
END loop;
close c;
end;

Just want to understand : whether while using the CURSOR FOR LOOP, is the index variable not a cursor attribute, if so why? could someone plz expalin this....

Answer 1

c_Emp is not the cursor, its a record with felds for each column in the SELECT statment

c_Emp is similar to the emp_record from your second example.

Answer 2

Even while using a FOR loop the cursor has to be explicitly defined. A sample use of FOR loop with a cursor would look like below:

declare
 cursor c1 is select a from table;
begin
 FOR b in c1
 loop
  <required logic>
 end loop;
end;

Answer 3

To get the index in a for loop, you can add the rownum pseudocolumn in the select clause of implicit cursor.

SET serveroutput ON;

BEGIN
  FOR c_Emp IN (SELECT e.*, rownum FROM emp e)
  LOOP
    dbms_output.put_line('The record processed by the cursor ' || c_Emp.rownum);
  END LOOP;
end;

Answer 4

Try this:

SET serveroutput ON;

DECLARE
x NUMBER :=0 ;
BEGIN
FOR c_Emp IN (SELECT * FROM emp)
LOOP
x := x+1;
dbms_output.put_line('The record processed by the cursor ' || x);
END LOOP;
-----
IF x>0 THEN
dbms_output.put_line('Cursr was opened');
ELSE
dbms_output.put_line('Cursr was not opened');
END IF; 

end;