Loop on an explicit cursor

To loop on an explicit cursor we take advantage of the fact that when we try to read the first element next to the end of a cursor we do not get an exception back, but we simply have the cursor property NOTFOUND set to true.

Here is an example:

declare
  cursor lc_countries is select * from countries;
  lr_country countries%rowtype;
begin
  open lc_countries;

  loop
    fetch lc_countries into lr_country;
  
    if lc_countries%notfound then -- 1.
      dbms_output.put_line('End of cursor');
      exit;
    end if;
  
    dbms_output.put_line(lr_country.country_name);
  end loop;
  
  close lc_countries;
exception
  when others then -- 2.
    dbms_output.put_line('Something went wrong ' || sqlerrm);
    close lc_countries;
end;

1. if the fetch reports that no row is available, setting the cursor property NOTFOUND to true, we end the loop.
2. it's a good idea to consider that we could have some unexpected exception after we open the cursor and before we could close it in the normal execution flow. Checking for exceptions we ensure we properly close it.

More information on data retrieval in PL/SQL in chapter 15 of Oracle PL/SQL Programming, fifth edition, by Steven Feuerstein.