[DB-SIG] SQL statement parse for Oracle

[Magnus Lyckå]

At 11:01 2001-12-14 -0700, Anthony Tuininga wrote:
>The problem with creating a stored procedure is that you lose complete 
>control. If you wanted to, for example, print something to stdout for each 
>row processed (or set of rows processed) you would not be able to do this.

There are other problems with stored procedures as well.
Last time I looked, there was no standard for stored
procedure language. Oracle use PL/SQL, Sybase use Transact
SQL etc. Maybe this has been solved with SQL99 and been
implemented by all major players and upgraded by all major
clients while I looked the other way? ;-) No?

So, if you need to support several different RDMBS with your
app, stored procedures will make life more difficult for you.

Even if you only use Oracle today, you might earn millions
in a large installation if you don't have a system which is
prohibitively expensive to move to DB2 or something else.
Stored procedures adds to the cost of a conversion. (One
client of mine switched a system with tens of thousands of
users from Oracle to DB2 since the licence costs were ten
times higher for Oracle...)

Last time Oracle tried to sell me a big database, they talked
about how you could use PL/SQL in both their client tools and
in the server, and how you could write your code once and then
move it between server and client to increase performance or
whatever was more important at the time.

I can surely see why Oracle thinks I should work like that.
Talk about lock-in.

But if you normally write your code in Python, and not in
Oracle 2000 or whatever it's called these days, it IS an extra
burden to switch to another language and another environment.
I don't think it requires any major intellectual effort, but
you get some more things to keep track of. Especially if you
have many installations, it's nice if you can keep the
administrative and technical support burden to a minimum.
Not having to worry about stored procedures can be one
ingredient in such a minimalistic strategy.

BTW, I recently bought and read (part of) Joe Celko's SQL
for smarties. (BTW, have you noticed that Celko bears a
striking resemblance to Flash Gordon's enemy Emperor Ming? :)
Apart from the fact that the book is written in a really
sloppy way, with little mistakes and unclear references etc,
I really feel sad when I see the attempts to write "advanced"
code in SQL. What would be trivial in Python soon gets very
"advanced" or at least ugly in SQL.

/Magnus


-- 
Magnus Lycka, Thinkware AB
Alvans vag 99, SE-907 50 UMEA, SWEDEN
phone: int+46 70 582 80 65, fax: int+46 70 612 80 65
http://www.thinkware.se/  mailto:magnus@thinkware.se