using cursor attributes in a CURSOR FOR LOOP
Solution 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.
Solution 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;
Solution 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;
Divas
Updated on September 13, 2020Comments
-
Divas over 3 years
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....