Re: Do you use PL/SQL

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

-- 
Marc Blum
mailto:<nachname>@marcblum.de
http://www.marcblum.de