4 Replies Latest reply: Apr 9, 2010 11:56 AM by Solomon Yakobson RSS

    catch when no rows are updated

    694243
      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?