I keep having this horrible nightmare where this big, "flexible", "write-once, run (poorly) everywhere" application full of embedded sql comes to get me.

If you actually read my post, I don't like having SQL embedded directly in the application, either. I much prefer having an abstraction layer, wherein all the code that has SQL in it is off to the side in a (probably custom, unless your needs are quite simple) module that the rest of the code uses.

You end up with the next developer who has to touch that bit of code spending three days figuring out how it works first, instead of three minutes.

Yes, but only if you assume (s)he has not previous knowledge of the technology in question (i.e. PL/SQL, the Oracle database). In my opinion, that makes her/him unqualified for the role as developer for that platform. Besides, those three days spent learning a new technology could be the best investment that person made in a long time.

Apparently your experience with stored procedures differs from mine. Considerably. I'm talking about someone who is *comfortable* with SQL, someone that other people on the application's mailing list turn to with their hardest SQL-related questions, spending three days (well, three shifts) figuring out how on earth this set of inter-related stored procedures work. And he's still not confident changing them that he won't break anything.

Maybe I just ran into some very-badly-written stored procedures (and, I probably should mention at this point that it was not on Oracle, although I doubt if that makes much difference), but that was my experience, and it left a really bad taste in my mouth. Each of those several multi-page stored procedures could have been a very small snippet of code in a high-level language and one or two short SQL queries, coming altogether to six or eight lines, and instead it runs onto five or six *hundred* lines, without comments, because it's doing stuff in SQL that SQL was never designed for.

SQL is designed for getting information from the database and putting information into the database. When you start messing around with 30+ variables and your loops and conditionals are nested more than about two deep, it's time to use a language that was designed for writing such logic. Use the right tool for the job.