Re: Do you use PL/SQL

From: Robert Klemme <shortcutter_at_googlemail.com>
Date: Mon, 21 May 2007 12:15:14 +0200
Message-ID: <5bd9p4F2kjla8U1@mid.individual.net>

On 21.05.2007 07:55, Arto Viitanen wrote:
> Ana C. Dent wrote:

>> Doug Davis <douglass_davis_at_earthlink.net> wrote in 
>> news:1179630172.123167.246530_at_o5g2000hsb.googlegroups.com:
>>
>>>
>>> I will be teaching a PL/SQL class, and I wanted to get some opinions.
>>> I read about PL/SQL, but wanted some practical advice from people who
>>> have used it.
>>>
>>>
>>> 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.)
>>
>>
>> I contend that PL/SQL reduces network traffic by significant amounts.
>> With SQL you can't do any type of flow control (IF THEN  ELSE).
>> With any 3GL lanuage, the results set must be returned back to the 3GL
>> before simple flow control can occur.
>> While the identical results can be obtained using either approach,
>> I believe that PL/SQL solution(s) can scale to MUCH large databases
>> and/or many more application users.

>
> No one has mentioned that Oracle supports other languages for stored
> procedures also. I have used Java, but I guess Oracle in Windows
> supports C# also. In Linux (and I guess other OSs also) Oracle has
> built in Java virtual machine (actually I guess it compiles Java to C
> or similar), so with Java you get the reduced network traffic.
>
> I had a set of queries that were made using an object relational mapper
> written in Java. This means that the queries are actually a set of Java
> methods. When I moved the methods to Oracle as stored procedures, the
> execution was on some cases over three times faster. Also, since the
> methods were run inside the Oracle, I did not have to open JDBC port
> to the world.

To me your posting demonstrates very well why I would choose PL/SQL - at least for all tasks that are close to the DB (i.e. maintenance scripts, jobs etc.): you can simply rely on it being there which you cannot with Java, C# etc. :-)

Kind regards

        robert Received on Mon May 21 2007 - 05:15:14 CDT