Re: Are java stored procedures worth it?

Answers to Eoin Gardiner's questions interspersed between the text of his message ...

karsten_schmidt8891_at_my-deja.com wrote:

> Matthew,
>
> I am _not_ using java stored procedures right now,
> but I can see them being useful for performance tuning jdbc
> applications. just take an existing data-intensive piece of java code,
> drop it into the database, and you are done.
>
> The other option would be to port this particular method to pl/sql,
> which would be a lot more trouble: a rather dramatic shift in paradigms.
>
> Karsten
>
> In article <8v38b7$b86$1_at_nnrp1.deja.com>,
> Matthew Fuller <matthewlf_at_my-deja.com> wrote:
> > In article <8v2ghl$prs$1_at_nnrp1.deja.com>,
> > Eoin Gardiner <eoin7_at_my-deja.com> wrote:
> > >
> > >
> > > I need to evaluate the use of java stored procedures, which I have
> > > never used before.
> > >
> > > I will be trying the examples from the Oracle Technology Network,
 but
 I
> > > also want to solicit input from people who have experience of using
> > > java stored procedures, via the following questions:
> > >
> > > Are there clear advantages of using java stored procedures over
> > > traditional PL/SQL ones?

The main advantage (IMHO) is that java stored procedures give you easy access to the world outside of the database engine - something that is _very_ difficult (though not impossible) to do with PL/SQL. The Oracle JVM also supports JDK 1.2 so you can use all the associated technologies like RMI, JDBC, networking, etc from your stored procedures. Quite powerful, I think.

>
> > >
> > > It seems that they are more complicated to build, needing a
 compilation
> > > in java, and a PL/SQL wrapper, whereas PL/SQL stored procedures can
 be
> > > built at a snap from a script.
> > > Does this make creation and maintenance much more difficult?

JDeveloper comes with a deployment tool. Just click on the appropriate menu item and your PL/SQL wrapper is created. In any case, the wrapper is _very_ simple (and short).

>
> > >
> > > Are there any performance gains or drops with java stored procedures
> > > over PL/SQL?

I haven't done any measurements, I can't say.

>
> > >
> > > The application under development is a web application written using
> > > java (including EJBs).
> > > Is this a reason to use java stored procedures?

EJBs are different from stored procedures, even though the EJB java classes are also loaded into the database engine just like stored procedures. The question (I think) should be whether to use Oracle's EJB server or a different EJB server.

>
> > > Is there a possibility of direct calls from client java to java
 stored
> > > procedures?

You call a java stored procedure exactly the same way as you call a PL/SQL procedure. The JDBC provides methods (via CallableStatement) for executing database stored procedures. There is no difference between calling a database stored procedure written in java or PL/SQL.

>
> > >
> > > Any feedback on your experiences greatly appreciated.
> > >
> > > Sent via Deja.com http://www.deja.com/
> > > Before you buy.
> > >
> >
> > I am currently in a similar situation. I don't have any real-life
> > advice for you, but here is a quote from "Oracle8 Design Using UML
> > Object Modeling" (Dorsey and Hudicka, Oracle Press):
> >
> > "...Uncompiled Java code offers reasonably comparable performance to
> > PL/SQL, though not quite as good, given the number of years that have
> > gone into the optimization of PL/SQL within the server.
> > However, compiled Java code can execute up to ten times faster than
> > PL/SQL..."
> >
> > This is in the "Server Side Code" section of Chapter 1.
> >
> > The authors also elude to the fact that although PL/SQL will be around
> > and supported for quite some time, the trend will be to move towards
> > Java. The obvious reasons are open standard/non-proprietary.
> >
> > I would probably pay money to see a list of all the possible access
> > methods into the database (i.e. server side .vs. client side,
> > compiled .vs. ucompiled, SQLJ .vs. PL/SQL, etc.) ranked and weighted
 in
> > order of performance to be able to factor that into design
> > considerations. Man, life was much easier when PL/SQL was the obvious
> > choice for most data access.
> >
> > Hope you get some more information. I'll be keeping my eyes on this
> > thread.
> >
> > Matt.
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
> >
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.

Cheers,
Avi. Received on Wed Nov 22 2000 - 00:39:45 CST