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.
Sanity? Oh, yeah, I've got all kinds of sanity. In fact, I've developed whole new kinds of sanity. You can just call me "Mister Sanity". Why, I've got so much sanity it's driving me crazy.
| [reply] |
.. 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.
That sounds more like bad use of technology than bad technology itself.
SQL is designed for getting information from the database and putting information into the database.
Yes. But we're also discussing PL/SQL, which was designed to do a whole lot more than data retrieval.
Use the right tool for the job.
Exactly. And apparently when using Oracle, that will often involve PL/SQL.
Andreas
--
| [reply] |