Office applications Toolkit

Integrating Oracle and Java: Optimising your development platform for ERP apps

Tags:
ERP,
Oracle,
Java

Scott Robinson Tech Republic

Published: 06 Jun 2003 11:05 BST

You can safely implement an external procedure with Java, an interpreted language, without configuring a listener (as you must when doing external procedures in a compiled language). The Java code does not run as a separate process. Oracle even provides an area in the database address space for the execution of the procedure -- the Java Virtual Machine. There is little that PL/SQL can do for you, external procedurewise, that Java won't do better.

For example (and this is the most obvious), PL/SQL isn't going to do you much good in coping with the external operating systems that your database interfaces are running on, and the platform-independent Java was made for such tasks. You have new possibilities open to you that PL/SQL never provided: A JSP can run operating system programs from within the database. This is useful when your external procedure is sensitive to database run-time conditions.

Why use JSPs other than for interface tasks and OS programs?
There are several reasons why Java is just plain better than PL/SQL for external procedures. For one thing, it's much more robust than PL/SQL. Java has hundreds of classes, making interfaces of considerable functionality possible. You simply have far more options with Java than with PL/SQL.

JSPs are also preferred over external routines based on C or other compiled languages. Why? Because when you implement a noninterpreter routine, you must deal with significant overhead. One nontrivial matter is that error-handling (for which ERP requires a higher standard due to the increased number of system failure points and the number and diversity of users accessing a database) is much harder to implement.

Providing the user with a meaningful error message via the application is a task that must be done from scratch. And there's the additional consideration of external compiled procedures being outside the processes of the server, so bugs occurring in the external routine when many people are using it are virtually untraceable. It is also true that a compiled external routine, storing its global variables via DLL caching, can lose those variables.

With Java, you're getting all the power and cohesion of a compiled external routine without these difficulties. Java classes handling variable values are secure, because there is no hand-off to store them; application error routines that report to the end user are easily implemented (Java is designed to favor users in reporting errors); and tracing is built in and almost effortless to report.

When would you not want to use Java for external procedures?
The most obvious reason not to use Java for an external procedure would be the KISS principle (keep it simple, stupid). For simple procedures requiring only a handful of PL/SQL lines, it would be silly to use Java (which generally will require much more code than PL/SQL for most tasks). To use Java when there is no clear advantage, operating systemwise or utilitywise, is to complicate your programming unnecessarily, making life difficult for down-line maintenance programmers (who will surely take your name in vain for doing so).

In addition, the performance gains realized by executing a stored procedure in the database address space (you see this improvement in actual database I/O) is offset by the fact that PL/SQL simply executes more efficiently with SQL, because they are so well integrated. The data type conversion that must occur when Java speaks to SQL doesn't need to happen between PL/SQL and SQL, so Java by definition can't be quite as efficient.

This is a trade-off, and it's one you need to consider in designing your external procedure. A good rule of thumb is to use Java when you have a practical reason for doing so and when the efficiency pendulum swings in Java's favor.

Go to section: