CODEWITHSUNDEEP
databaseoracle-databaseplsqloracle11gtoad
Random guy
Random guy

Reputation: 923

Cursor is returning more data that is available in table

I have query 

select * from ot.city_vw;

The data coming is: enter image description here

But when I write a pl/sql block to get the data then

declare
v_data OT.city%rowtype;
CURSOR cur1  is
select * from ot.city_vw;
begin

open cur1;
loop
fetch cur1 into v_data;
dbms_output.put_line(v_data.city_id);
dbms_output.put_line(v_data.city_name);
 EXIT WHEN cur1%NOTFOUND;
    END LOOP;
    CLOSE cur1;
end;
/

the last table data is coming twice as below in picture:

enter image description here

what is the problem with my cursor? why is the last data coming twice?

Upvotes: 2

Views: 35

Answers (1)

Littlefoot
Littlefoot

Reputation: 142705

Move EXIT up.

open cur1;
loop
  fetch cur1 into v_data;
  EXIT WHEN cur1%NOTFOUND;                      --> here
  dbms_output.put_line(v_data.city_id);
  dbms_output.put_line(v_data.city_name);
END LOOP;
CLOSE cur1;

Or, even better, user cursor FOR loop:

begin
  for v_data in (select * from ot.city_vw) 
  loop
    dbms_output.put_line(v_data.city_id);
    dbms_output.put_line(v_data.city_name);
  end loop;
end;

Doesn't it look simpler? Oracle does all the dirty job for you (declaring a cursor variable, opening the cursor, exiting the loop, closing the cursor). I suggest you use it whenever possible.

Upvotes: 3

Related Questions