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: Robert Klemme <>
Date: Wed, 23 May 2007 13:34:42 +0200
Message-ID: <>

On 23.05.2007 01:45, wrote:

> 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 Received on Wed May 23 2007 - 06:34:42 CDT

Original text of this message