Sql%notfound

I've declared a cursor c3 that I know pulls back no rows at the moment, however when I run the procedure I keep getting the message 'Rows deleted'. I've checked the no of rows with SQL%ROWCOUNT and get 1 as the result. Can anyone explain why and how I should be writing the code??

for rec in c3 loop
delete from tester where dbid=rec.dbid;
end loop;
if SQL%NOTFOUND then
dbms_output.put_line('No rows to delete');
else
dbms_output.put_line('Rows deleted');
end if;

Hi,
Please post your script to resolve this issue.
Thanks.
Regards,
Kumar. RP

DECLARE
v_dbid number;
v_inst_num number;

cursor c2 is
select * from stats$database_instance di
where instance_number = v_inst_num
and dbid = v_dbid
and not exists (select 1
from stats$snapshot s
where s.dbid = di.dbid
and s.instance_number = di.instance_number
and s.startup_time = di.startup_time);

BEGIN

-- Get info about the Database and instance
--select dbid into v_dbid from v$database;
v_dbid := 3824701041;
--select distinct instance_number into v_inst_num from stats$database_instance where dbid = v_dbid;
v_inst_num := 1;

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

END;
/

Change your last part of the script to the following and see what happens:

Code:

---

for rec in c2 loop
  delete from stats$database_instance where snap_id=rec.snap_id;
end loop;
commit;
if SQL%NOTFOUND then
  dbms_output.put_line('No hanging SQL to delete');
elsif NOT SQL%NOTFOUND then
  dbms_output.put_line('Hanging SQL deleted');
else
  dbms_output.put_line('Hey, what exactly do you want me to show???');
end if;


---

The cursor atribute SQL%NOTFOUND has nothing to do with your cursor C2!!! When you call the atribute SQL%NOTFOUND your cursor C2 doesn't exist any more, it is closed allready!

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;

Hi,

as you know SQL%NOTFOUND,Sql%found,Sql%rowcount are basically implicit cursor attributes, these attributes will execute and value is captured whenever transaction of DML statement is executed with out any exceptions like value error (except no data_found).

in ur case delete statement is executing hence u r getting value in cursor attribute.

Quote:

---

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

In your case, I would do something like:

Code:

---

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;

---

Ah - slowly the fog lifts ;)
Thanks for the help - it was driving me nuts!!