Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: Do you use PL/SQL

Re: Do you use PL/SQL

From: <>
Date: 23 May 2007 15:11:07 -0700
Message-ID: <>

On May 23, 2:31 pm, Frank van Bortel <> wrote:
> Hash: SHA1
> www.douglassdavis.comwrote:
> > On May 23, 3:21 am, Frank van Bortel <>
> > wrote:
> >> Hash: SHA1
> >> Doug Davis wrote:
> >>> On May 20, 5:08 am, Frank van Bortel <>
> >>> wrote:
> >>>> Hash: SHA1
> >>>> Doug Davis wrote:
> >>>>> I will be teaching aPL/SQLclass, and I wanted to get some opinions.
> >>>>> I read aboutPL/SQL, but wanted somepracticaladvice from people who
> >>>>> have used it.
> >>>>> 1. Why usePL/SQLinstead 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
> >>>>> withPL/SQL(or have heard some one do withPL/SQL?)
> >>>>> thanks.
> >>>> When asking these questions, I seriously begin to question
> >>>> the authority to teach classes.
> >>>> These are exactly the type of questions *you* should be
> >>>> able to answer, as an instructor!
> >>> thanks for your opinion.
> >> You're welcome.
> >> One thing I haven't seen in this thread, is the possibility to
> >> write database independent applications.
> >> When executed correctly, all the application does is make calls
> >> to (packaged) procedures. (packaged in brackets, as Oracle is
> >> the only RDBMS that supports that, whereas every RDBMS system
> >> with procedural extensions supports functions and procedures).
> >> The application then does not have to "know" the ins and outs
> >> of the RDBMS, and every RDBMS programmer can implement specifics
> >> in the procedures, using the possibilities of the RDBMS to the
> >> full extent of the product.
> >> Obviously, such an approach can only be done with whatever language
> >> the RDBMS offers natively. For Oracle, that would be either PL/SQL,
> >> or Java. Rule of thumb here (and be careful not to promote ROT to
> >> truth!) to use Java for CPU intensive stuff, and PL/SQL for data
> >> intensive stuff.
> >> Sadly, it's all to common that apps get "re-engineered" towards
> >> db independence after the initial release(s). It may become
> >> clear from the above, such an approach requires a hell of a
> >> lot of thinking and planning, and thus is seldom (if at all)
> >> seen, while "taking too long", "being too expensive", "being
> >> too difficult".
> >> <sarcasm>
> >> No - having to patch systems on a monthly basis for the life
> >> of the product (5 ~ 7 years), that's smart, inexpensive, fast.
> >> </sarcasm>
> > so i take it that you are not really a fan of "middleware" written in
> > some other language that provides an interface that applications can
> > use (via CORBA or something) and talks to the database via SQL.
> Hmmmmm... depends. Not in the way it is found commonly.
> Many (not all!) java programmers treat databases in general as
> a bin with data. They create their own locking mechanism, sort
> on the application server, even join tables after pulling them
> over the network... And of course, it's the database that is slow...
> And they cannot understand why it doesn't scale (it worked like
> lightning in our labs...)
> And they cannot understand why sysadmins start pulling their
> hairs out, because huge *nix machines suffocate from the
> lack of memory (did you set -MX2048, or didn't you?). They
> just do not understand their nice app is not the only thing
> running.
> But to elaborate on the previous, there's nothing against
> presentation and high-level business logic in the application
> server; by all means! Just make it common, so that you can
> switch the backend. From MySQL to MS/SS to DB2 to Oracle.
> And middleware calls packaged PL/SQL procedures over jdbc, I hope.
> - --
> Regards,
> Frank van Bortel
> Top-posting is one way to shut me up...
> Version: GnuPG v1.4.1 (MingW32)
> iD8DBQFGVIidLw8L4IAs830RAmxIAJ9mwCNqFIEj4x637m+HrjVDdjsg1wCfdBg5
> sO96MH5qFU8z+N9ku6yKUrA=
> =f7Cu
> -----END PGP SIGNATURE------ Hide quoted text -
> - Show quoted text -

In practice, you cannot make your application as database independent by writing lots of stored procedures in PL/SQL. That's the reason people avoid PL/SQL because it is a proprietary Language of Oracle. If you are a software vendor and develop a product for many databases, you want to use as little one data specific code, I am sure Frank, Dan and other Oracle gurus will say otherwise, but that is reality. Look at vendors like SAP, EMC Documentum, they do not use any PL/SQL. They have abstracted a common SQL, and they stick to it. May be their applications run little slower than if they developed all their database code in Pl/SQL for Oracle database, but I can assure this slowness is in terms of mill second and not minutes. If you write inefficient queries, that is where you see order of magnitude slowness in performance. While PL/SQL is a good language for Oracle databases, it lacks object-orieneted classes of C++ and Java , a serious limitation once you get hang of object-oriented programming. It does not have interprocess communication facilities of middleware products like CORBA, EJB, DCOM etc, which si another limitation in large software projects. Received on Wed May 23 2007 - 17:11:07 CDT

Original text of this message