Re: Do you use PL/SQL

From: DA Morgan <damorgan_at_psoug.org>
Date: Tue, 22 May 2007 15:25:15 -0700
Message-ID: <1179872716.487812@bubbleator.drizzle.com>

Doug Davis wrote:

> On May 20, 6:12 am, Marc Blum <b..._at_marcblum.de> wrote:

>> On 19 May 2007 20:02:52 -0700, Doug Davis <douglass_da..._at_earthlink.net> 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.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Tue May 22 2007 - 17:25:15 CDT