Is it appropriate to raise exceptions in stored procedures that wrap around CRUD operations, when the number of rows affected != 1?

Question

This is a pretty specific question, albeit possibly subjective, but I've been using this pattern very frequently while not seeing others use it very often. Am I missing out on something or being too paranoid?

I wrap all my UPDATE,DELETE,INSERT operations in stored procedures, and only give EXECUTE on my package and SELECT on my tables, to my application. For the UPDATE and DELETE procedures I have an IF statement at the end in which I do the following:

IF SQL%ROWCOUNT <> 1 THEN
  RAISE_APPLICATION_ERROR(-20001, 'Invalid number of rows affected: ' || SQL%ROWCOUNT);
END IF;

One could also do this check in the application code, as the number of rows affected is usually available after a SQL statement is executed.

So am I missing something or is this not the safest way to ensure you're updating or deleting exactly what you want to, nothing more, nothing less?

Answer 1

I think this is a fine way to go. If the pl/sql proc is expected to always update/delete/insert a row and it's considered an error otherwise, then what better place to put this check than in the pl/sql proc itself? That way, no matter what client side code (C#, JAVA, PHP, Rails, etc.) uses this proc, you have this error check centralized in one place.

However, I'm not sure you need the check for an insert. If the insert fails, you should get some sort of DB exception, so no need to check for it explicitly unless you are wrapping the error in some other error message and re-raising it.

Answer 2

In most cases I'd use an ORM like Hibernate, which does a similar thing in order to handle Optimistic locking. Also it will use the PK in the where clause.

So I would consider this kind of stored procedure a waste of time: - A lot of effort for minimal benefit - Makes usage of tools like ORMs harder, which solve more and more important problems.