But isn't the nightmare a human, collaboration problem rather than a technical one? Assuming that the purpose of these stored procedures is to ensure data integrity at a lower level of abstraction and that you have appropriate staff to maintain that layer, I don't see where the nightmare part comes in.

In my experience, and perhaps with my bias as someone who's very comfortable with (relational) databases, I have seen plenty of nightmarish scenarios involving, say, Hibernate.