Sql%notfound

[jmodic]

Originally posted by cleggfr
Thats what I thought - but if I put the IF statement inside the cursor I get no output at all??:

for rec in c2 loop
delete from stats$database_instance where snap_id=rec.snap_id;
if SQL%NOTFOUND then
dbms_output.put_line('No hanging SQL to delete');
else
dbms_output.put_line('Hanging data deleted');
end if;
end loop;
commit;

Change your SQL%NOTFOUND into c2%NOTFOUND inside the cursor for-loop.

But if your cursor doesn't return any rows then the body of the loop will never be executed, hence you still won't get any output .

In your case, I would do something like:

DECLARE
  ....
  v_found BOOLEAN := FALSE;
BEGIN
  ...
  for rec in c2 loop
    v_found := TRUE;
    delete from stats$database_instance where snap_id=rec.snap_id;
  end loop;
  if v_found then
    dbms_output.put_line('Hanging data deleted');
  else
    dbms_output.put_line('No hanging SQL to delete');
  end if;
  commit;
  ....
END;