Re: Do you use PL/SQL

On May 19, 8:02 pm, Doug Davis <douglass_da..._at_earthlink.net> wrote:
> I will be teaching a PL/SQL class, and I wanted to get some opinions.
> I read about PL/SQL, but wanted some practical advice from people who
> have used it.
>
> 1. Why use PL/SQL instead of just sending SQL queries from a program
> written in a procedural language on the client side (Java, Visual
> Basic, C++, anything.)
>
> 2. What are some examples of "real-world" things that you have done
> with PL/SQL (or have heard some one do with PL/SQL?)
>
> thanks.
>
> --http://www.douglassdavis.com

First let me say, I agree with most of all the other posts up to here.

Second, I have to admit in the past I've bad-mouthed PL/SQL for various reasons. I no longer am of that opinion.

Third, I rarely use PL/SQL.

That said, here's some more thoughts:

Nowadays, Oracle is the most-used db by many measures, and there is a large pool of expertise available. In particular, Tom Kyte has been a technically oriented evangelist, and has written several books of biblical importance. I would urge anyone using or teaching the Oracle db to be intimately familiar with those works, as well as asktom.oracle.com. PL/SQL is the language the Oracle supplied procedures are written in, and there is some value to limiting the variety of languages used, when it is an arbitrary decision regarding language functionality. It certainly helps to have master-level examples available rather than inventing a Rube Goldberg wheel.

A downside of all the client-side query languages is the tendency to view the db as a black box or mere persistent data store. We see over and over in this group people assuming the way they've been shown things should work is the best way to do it in Oracle. This extends to very large and expensive enterprise software - even some Oracle corp sells. Bad assumption! A collateral problem is that the transaction model Oracle uses by default is different than how other db's do it, so people using those tools tend to try to force their apps into the wrong model.

>From a performance standpoint, it's generally accepted that doing
things in SQL is preferred over PL - at the very least, there's context-switching going on to get into the PL engine, and with a relational database, it is imperative to think in terms of sets rather than procedures. However, while of course you can write bad PL code, there is a more abstract danger of a client-side SQL generator generating the SQL in the worst possible manner. A common example of this is bind variable usage - you can write bad PL code (look up dynamic SQL in the docs), and similarly you can generate SQL with literals. On the db side, this means heavy parsing, which would be avoided by having identical sql with bind variables, as opposed to SQL identical but for using different literals (there are even db settings to try to hack a fix to this for commercial packages written "wrong." Yuck!). It is very important to make this clear to students you are teaching. Having a properly written procedure in the database avoids problems such as this. It is very difficult (that would be, "impossible") to control code written by many different people at widely varying skill levels.

As far as java procedures in the db, note there is also additional overhead for memory and startup time. But it can do things PL can't, and there is something to be said for java also having a large user community. That is quite different than client-side procedurality, though.

A danger with PL/SQL I've seen is that many people can't quite tell the difference between the procedural and non-procedural parts. You should be sure your students get that!

Client side tools often suffer from driver-hell, too. Which ODBC was that, again?

And of course, so many tools download the data then process it, when it should have been processed on the server.

jg

--
@home.com is bogus.
http://blog.support-intelligence.com/