SQL & PL/SQL

Hello!

I want to catch an exception, or be informed somehow when an UPDATE statement does not update any rows.

In more details - I have a script with UPDATES and DELETES. Some of them update no rows, because the primary key is not found. I want to write these unsuccessful statements to a log file (or log db table).

I tried with this:

BEGIN
update t1 set col_content = 'blabla' where col_primkey = 1;
delete from t2 where col_primkey = 111;
EXCEPTION
  when no_data_found then
    dbms_output('exception: ' || dbms_utility.format_error_backtrace);
END;

and it didn't work, of course...

Furthermore, let's suppose, that the UPDATE statement does not update any rows, but the DELETE statement will be successful. If I catch the exception from the UPDATE statement, then the DELETE statement will not be executed. My purpose is to log the failure of the UPDATE statement and then execute the DELETE statement!

is that possible?

the only thing I can think of right now is:

BEGIN
select 1 into cc from t1 where exists (select * from t1 where col_primkey = 1);
if (cc = 1) then
  update t1 set col_content = 'blabla' where col_primkey = 1;
else
  dbms_output('primary key not found');

select 1 into cc from t1 where exists (select * from t2 where col_primkey = 111);
if (cc = 1) then
  delete from t2 where col_primkey = 111;
else
  dbms_output('primary key not found');

EXCEPTION
  when no_data_found then
    dbms_output('exception: ' || dbms_utility.format_error_backtrace);
END;

do you have other ideas?