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 04:50:48 -0700
Message-ID: <>

On May 23, 7:34 am, Robert Klemme <> wrote:
> On 23.05.2007 01:45,www.douglassdavis.comwrote:
> > On May 22, 6:25 pm, DA Morgan <> wrote:
> >> Doug Davis wrote:
> >>> On May 20, 6:12 am, Marc Blum <> wrote:
> >>>> On 19 May 2007 20:02:52 -0700, Doug Davis <> wrote:
> >>>>> 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.)
> >>>> First: when talking about PL/SQL, it always means to me a mixture of SQL and
> >>>> PL/SQL. We follow the first law according to Tom Kyte:
> >>>> - If you can do it in pure SQL, do it in pure SQL.
> >>>> - If you can't do it in pure SQL, do it in PL/SQL.
> >>>> - If you can't do it in PL/SQL, do it in Java, C or whatever...
> >>>> We use PL/SQL as an application server thier. The PL/SQL layer manages all data
> >>>> centric operations. Typically representation and user interaction remains to the
> >>>> 4GLs like Delphi and so on.
> >>>> From a deployability standpoint, putting business requierements into packages
> >>>> makes code management in dev, test and prod much more easier.
> >>>> Security models are much easier to implement.
> >>>> PL/SQL is tightly integrated with the database engine. You have the full power
> >>>> of ORACLE server at your fingertips.
> >>>> The only advantage for other languages seems to be when doing extremely CPU
> >>>> intensive algorithms. When throwing data around, PL/SQL is unbeatable.
> >>>>> 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?)
> >>>> For about 10 years we use with great success a PL/SQL-centric approach in dozens
> >>>> of big applications, individual solutions and software products.
> >>>> Typically it's the small projects, which don't follow this path.
> >>>> We do incredible big and complex applications. 100.000 and 100.000 and more
> >>>> lines of code, which implement not-so-simplistic business rules.
> >>>> Last shot was a software module to manage time series data, implementing a
> >>>> API-based approach, coded 100% in PL/SQL and SQL.
> >>>> In legacy systems, we successfully migrate code from the app layer to the PL/SQL
> >>>> layer, whenever possible.
> >>> thanks. real-world experiences such as this are always helpful to me.
> >>> PL/SQL does seem like it would be good for less CPU intensive tasks.
> >> As someone who has been in IT since 1969 and has more than a few years
> >> of experience with Oracle your response, the line above this, is an
> >> emetic. You truly do not understand PL/SQL. You seem to truly not
> >> understand relational databases. You seem to not have any background
> >> in Oracle concepts and architecture. And your students deserve to take
> >> this class from someone who, if not a subject matter expert, has at
> >> least a passing familiarity with the subject.
> >> The more you write the more you throw gasoline on the issue.
> >>> I would think that if you put business-logic in a client side app, you
> >>> would have the problems of more network traffic (although this seems
> >>> very dependent apon how much network traffic is inherent in the app,
> >>> it seems like it may not even be a problem for some).
> >> You just don't get it so let me spell it out. No database yet built and
> >> put into production has been accessed by only the designated front-end
> >> tool. It will be accessed by many tools and all but one of them will
> >> not have the business logic.
> >> You put business logic into the database to protect the data from people
> >> with SQL*Plus, MS Access, and yes even Crystal Reports. Doing so is far
> >> more efficient in terms of CPU, Disk I/O, network bandwidth, etc. Plus,
> >> in the database, we have tools that we can use to tune DML.
> >> The problem in application development these days is that people who
> >> have essentially no background in databases, such as you, make decisions
> >> based wholly upon their ignorance. No SQL developer would pretend to
> >> know how many layers belong in a Java application, or whether to use or
> >> not use beans. But front-end developers, reeking of ignorance, quite
> >> often think they can write SQL, and worse yet PL/SQL.
> >>> Also, if you did
> >>> a lot of SQL queries on the client side, and wanted to change and
> >>> develop another client, you may have to reprogram much of the business
> >>> logic too. So, i could see that being a problem.
> >> Don't worry. We have DBMS_ADVANCED_REWRITE and we rip your queries out
> >> of our databases and replace them with our own when they are too ugly.
> >> I would recommend that you pick up a copy of Tom Kyte's books and read
> >> them. And again that you find a good DBA to co-teach with you so that
> >> your students have a chance of being successful.
> >> Essentially every sentence you have written here demonstrates that you
> >> do not understand the subject. Please reconsider taking the assignment.
> > I'm not really sure how to reply here, you are creating things in
> > your mind that are not even being said.
> > i appreciate any advice you or any one else has on real-world issues
> > with using PL/SQL or what books to study, otherwise, you have made
> > yourself pretty much useless to me at this point. Take care.
> I can see this posting three times with timestamps differing one minute
> each. Please make sure to post just once. Thank you.
> robert

google groups keeps saying "an error occured while trying to post, please try again"

anyway, I tried to cancel it, sorry. Received on Wed May 23 2007 - 06:50:48 CDT

Original text of this message