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.
PL/SQL is an awful language to work with. What's up with having to declare variables before using them? Does it not even have multi-pass compilation?! And it works only with Oracle, which causes lock-in I'd rather avoid.
I'd rather create a service layer on top of my DB and access it through that. Then I have more control over logging, managing access, pubsub, etc.
Oh I'm sorry, didn't know we were playing word games. The above poster complained about having to declare the types of his variables as if that was a bad thing. I simply pointed why it was in fact a good thing - you don't now need to do tests to ensure that your function that expects an INT always gets an INT, the type system takes care of it for you.
Discussions at this level presuppose a certain amount of background knowledge. Do try to keep up.
The maintenance issue is tractable, I'm just saying that PL/SQL doesn't make it easier and probably makes it worse by being hard to unit test, refactor, etc (although this is very subjective and depends on the strengths of your team).
The scalability problem? Good luck getting 90%+ of your processing out of the database. Rewrite from scratch?