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....

Why not let us know what you are doing on what system and what language? — JimmyB
– JimmyB, Commented Aug 28, 2013 at 7:51
I guess you are referring to SQL on an Oracle DBMS. Correct? — JimmyB
– JimmyB, Commented Aug 28, 2013 at 7:52
Apologies if this is not clear, It is an Oracle RDBMS and I am executing this PL/SQL block on Scott schema. — Divas
– Divas, Commented Aug 28, 2013 at 8:34

schurik · Accepted Answer · 2013-08-28 08:57:00Z

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.

answered Aug 28, 2013 at 8:57

schurik

7,9582 gold badges26 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Divas Over a year ago

So is there any way to access the cursor attributes if a Cursor For loop is used?

schurik Over a year ago

in plsql you can access the attributes of an implicit CURSOR using SQL- prefix e.g. SQL%ROWCOUNT. But i'm not sure if that will work in a FOR loop. see docs

Baljeet · Accepted Answer · 2013-08-28 11:24:06Z

1

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;

answered Aug 28, 2013 at 11:24

Baljeet

4383 silver badges9 bronze badges

2 Comments

Divas Over a year ago

using just a select clause, I can define an implicit cursor in the For loop, if I define the cursor explicitly I think it falls under the second case. I searched in the doc, got to know that for an implicity defined cursors like this, the cursor attribute will be NULL, so using SQL%ROWCOUNT gives a NULL value.

AndyDan Over a year ago

Try what Baljeet suggests. It works. Replace his <required logic> with "dbms_output.put_line ('The record count is ' || c1%rowcount);" and see what happens.

gary sze · Accepted Answer · 2016-09-13 07:31:43Z

1

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;

answered Sep 13, 2016 at 7:31

gary sze

111 bronze badge

Comments

Mohamed Faramawy · Accepted Answer · 2022-04-19 07:54:43Z

0

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;

answered Apr 19, 2022 at 7:54

Mohamed Faramawy

1

2 Comments

Community Over a year ago

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.

Ana GH Over a year ago

This does not provide an answer to the question. Once you have sufficient reputation you will be able to comment on any post; instead, provide answers that don't require clarification from the asker. - From Review

user9770915 · Accepted Answer · 2022-07-14 18:30:43Z

SQL> create table product(
  2     product_id number(4)     not null,
  3     product_description varchar2(20) not null
  4  );

Table created.

SQL>
SQL> insert into product values (1,'Java');

1 row created.

SQL> insert into product values (2,'Oracle');

1 row created.

SQL> insert into product values (3,'C#');

1 row created.

SQL> insert into product values (4,'Javascript');

1 row created.

SQL> insert into product values (5,'Python');

1 row created.


SQL> create table company(
  2     product_id        number(4)    not null,
  3     company_id          NUMBER(8)    not null,
  4     company_short_name  varchar2(30) not null,
  5     company_long_name   varchar2(60)
  6  );

Table created.

SQL> insert into company values(1,1001,'A Inc.','Long Name A Inc.');

1 row created.

SQL> insert into company values(1,1002,'B Inc.','Long Name B Inc.');

1 row created.

SQL> insert into company values(1,1003,'C Inc.','Long Name C Inc.');

1 row created.

SQL> insert into company values(2,1004,'D Inc.','Long Name D Inc.');

1 row created.

SQL> insert into company values(2,1005,'E Inc.','Long Name E Inc.');

1 row created.

SQL> insert into company values(2,1006,'F Inc.','Long Name F Inc.');

1 row created.


SQL> DECLARE
  2    CURSOR cursorValue IS
  3      SELECT h.product_description,o.company_short_name FROM company o,product h
  4      WHERE o.product_id =h.product_id
  5      ORDER by 2;
  6    num_total_rows NUMBER;
  7  BEGIN
  8
  9    FOR idx IN cursorValue LOOP
 10      dbms_output.put_line(rpad(idx.product_description,20,' ')||' '||
 11      rpad(idx.company_short_name,30,' '));
 12
 13      num_total_rows :=cursorValue%ROWCOUNT;
 14    END LOOP;
 15    IF num_total_rows >0 THEN
 16      dbms_output.new_line;
 17      dbms_output.put_line('Total Organizations = '||to_char(num_total_rows));
 18    END IF;
 19  END;
 20  /
Java                 A Inc.
Java                 B Inc.
Java                 C Inc.
Oracle               D Inc.
Oracle               E Inc.
Oracle               F Inc.
Total Organizations = 6

PL/SQL procedure successfully completed.


SQL> drop table product;

Table dropped.


SQL> drop table company;

Table dropped.

    enter code here

While your answer is technically correct, it doesn't address the question the OP was asking almost 9 years ago. The OP was asking trying to understand a concept, you are providing a piece of code without context and without providing information about the concept the OP was trying to understand

Collectives™ on Stack Overflow

using cursor attributes in a CURSOR FOR LOOP

5 Answers 5

2 Comments

2 Comments

Comments

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

2 Comments

Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related