Re: Do you use PL/SQL

From: Arto Viitanen <arto.viitanen_at_csc.fi>
Date: Mon, 21 May 2007 08:55:11 +0300
Message-ID: <4651343d$0$3195$ba624cd0@newsread.funet.fi>

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.

-- 
Arto Viitanen, CSC Ltd.
Espoo, Finland

Received on Mon May 21 2007 - 00:55:11 CDT