Re: Do you use PL/SQL

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. 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). 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.

Another poster mentioned having a middle-tier in another language that handles business logic, which, I suppose, would serve the same purpose. Although I have connected to a DB with Java, it doesn't seem to fit quite as well, so i do understand why use PL/SQL in that sense.

The security thing seems as though it would be useful if you didn't want intermediate query results to be floating around on the network.

Thanks to every one else who mentioned websites and books to read and contributed their opinions and experience, I will definately go back and read all of that. Received on Tue May 22 2007 - 15:17:08 CDT