When should you use java stored procedures with an Oracle database … what are the drawbacks?

When should you use java stored procedures with an Oracle database … what are the drawbacks?

In the Oracle world the general order of development should be:

Wherever possible do it purely with SQL.
If you need more than SQL do it with PL/SQL.
If you need something that PL/SQL cant do, then use Java.
If all else fails use C.
If you cant do it with C, back slowly away from the problem….

PL/SQL stored procedures are an excellent way of moving your business logic to a layer that will be accessible by any integration technology. Business Logic in a Package (dont write stand alone Functions and Procedures – theyll grow over time in an unmanageable way) can be executed by Java, C#, PL/SQL, ODBC and so on.

PL/SQL is the fastest way to throw around huge chunks of data outside of pure SQL. The Bulk Binding features means it works very well with the SQL engine.

Java stored procedures are best for creating functionality that interacts with network or operating system. Examples would be, sending emails, FTPing data, outputting to text files and zipping it up, executing host command lines in general.

Ive never had to code up any C when working with Oracle, but presumably it could be used for integrating with legacy apps.

Only when you cant do it in PL/SQL ( or PL/SQL proves to be too slow, which would be pretty rare I believe ).

As a case study… We had a single java stored procedure running in production ( Oracle 9i ), it was originally written in java because at the time we thought java was cool, Something Ive long since changed my mind about. Anyway. One day, the DB crashes, after it reboots the java SP doesnt work. After much back and forth with oracle support, they dont really know what the problem is and the only suggestions they have involve much downtime. Something which wasnt an option. 30 minutes later I had rewritten the java SP in PL/SQL.

It now, runs faster, is oracle native , shares the same deployment process as other objects and is easier to debug.

PL/SQL is a very capable language. If you are writing Stored Procedures, please take the time to learn it rather than just doing things in java because thats what you know.

When should you use java stored procedures with an Oracle database … what are the drawbacks?

The main advantage is access to the APIs and language features not found in PL/SQL. For example, I have used them for regular expression processing, file/directory manipulation and XML parsing.

There are a number of disadvantages:

  • Poor tool support
  • Lack of control over the JVM
  • DBAs often arent trained in Java. In order to support your production code youll either either need to give your DBAs more training or hire Java-trained support staff

Moving the Java to an application server is often a better approach as this counteracts the disadvantages. There is excellent tool support, great control over the JVM and there are heaps of people trained up in the popular application servers so finding support staff is easy. There is the opportunity cost of the performance hit moving away from the database but keeping Java close to the database doesnt give you great performance gains anyway.

You definitely need a reason to use Java in the database over a) PL/SQL stored procedures or b) Java outside the database.

Leave a Reply

Your email address will not be published. Required fields are marked *