Re: Do you use PL/SQL

On May 22, 6:25 pm, DA Morgan <damor..._at_psoug.org> wrote:
> 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.

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. Received on Tue May 22 2007 - 18:45:28 CDT