CODEWITHSUNDEEP
oracle-databaseplsql
sunleo
sunleo

Reputation: 10943

Cursor of Oracle with Duplicate of final Record

Why am getting duplicate records ? pls correct me.Thanks in Advance.

declare
  clazzes_rec clazzes%rowtype;
  cursor clazzes_cur is select * from clazzes;
begin
  if clazzes_cur%isopen then
     dbms_output.put_line('Cursor is not open,trying to Open.... ... .. .');
  end if;

  open clazzes_cur;

  dbms_output.put_line('Cursor opened :');

  loop
    fetch clazzes_cur into clazzes_rec;
      dbms_output.put_line('id:'||clazzes_rec.id||':name:'||clazzes_rec.name);
    exit  when clazzes_cur%notfound;
  end loop;

  close clazzes_cur;

end;

Ouput :

Cursor opened :
id:1:name:leo1
id:2:name:leo2
id:3:name:leo3
id:4:name:leo4
id:4:name:leo4

PL/SQL procedure successfully completed

Upvotes: 2

Views: 2841

Answers (1)

Yaroslav Shabalin
Yaroslav Shabalin

Reputation: 1644

Just swap the lines:

loop
 fetch clazzes_cur into clazzes_rec;
 exit  when clazzes_cur%notfound;
  dbms_output.put_line('id:'||clazzes_rec.id||':name:'||clazzes_rec.name);
end loop;

When you have fetched the last record and trying to fetch the next clazzes_cur%notfound becomes true but before it has a chance to exit from loop you are outputting the last record once again.

Upvotes: 5

Related Questions