Wednesday, November 24, 2010

Calling an Oracle database procedure from Java with only ONE line of code

About a year ago I was working on an ADF application for one of our clients. One of the problems they faced was that they needed to call a lot of database procedures. It is not very hard to call a database procedure if you are familiar with the JDBC API, but somehow they were able to mess it up. And even if you do it right, you have to write quite a few lines of Java code for one database procedure call. How can we simplify this?

Wouldn't it be nice if you could copy the specification of a PL/SQL procedure or function and then... just call it? That is exactly how my DatabaseProcedure class works.

How to use it?

Suppose you have the following function in the database.

function hello_world
( p_name in varchar2
) return varchar2 as

begin

  return 'Hello ' || p_name;

end hello_world;

First, you define a DatabaseProcedure constant by simply copy-pasting the specification of the PL/SQL procedure, like this:
// Prepare a static DatabaseProcedure object as follows. This object can be reused
// multiple times.
private static final DatabaseProcedure HELLO_WORLD_PROC =
    DatabaseProcedure.define("function hello_world" +
                             " ( p_name in varchar2" +
                             " ) return varchar2");

Then, you can call it, simply by using the call() method.
Connection connection = ...; // You either need a java.sql.Connection
                             // or a oracle.jbo.server.DBTransaction. Note that
                             // you can get a DBTransaction object by calling
                             // getDBTransaction() from the ApplicationModuleImpl.
String name = "Paco"; // The input value.

// A DatabaseProcedure object has a call method. This method accepts a Connection or
// DBTransaction as first parameter, the following parameters are the parameters to the
// database procedure / function (varargs, positionally).
String result = (String) HELLO_WORLD_PROC.call(connection, name).getReturnValue();
// result == "Hello Paco"

Output parameters

Output parameters are also supported. Example:
// A DatabaseProcedure object is prepared just as before, but this
// time we use out parameters.
private static final DatabaseProcedure EMP_DETAILS_PROC =
    DatabaseProcedure.define("procedure get_employee_details" +
                             " ( p_id in number" +
                             " , p_name out varchar2" +
                             " , p_address out varchar2" +
                             " , p_age out number)");

To call it:
Connection connection = ...; // Same as before
Integer id = 1; // The input parameter

DatabaseProcedure.Result result = EMP_DETAILS_PROC.call(connection, id);

String name = (String) result.getOutputValue("p_name");
String address = (String) result.getOutputValue("p_address");
Number age = (Number) result.getOutputValue("p_age");

TABLE, ARRAY and custom types

It is possible to use custom types, arrays, etc, but this is a bit more complicated. For that, I have included the methods: registerCustomParamType and registerArrayType. See this blogpost on TABLE and custom types and the Javadoc of the attached class for more information.

Performance

"This method looks cool! It must impose a severe performance penalty, right?" Fortunately, that is not the case! The PL/SQL specification you use is parsed by the Java code only once (using regular expressions). No database call is made until you actually use the call() method. At that point it simply uses ONE ordinary JDBC call. So no extra calls have to be made and the behavior is the same as ordinary JDBC. Therefore, it is very unlikely that you will encounter any measurable performance penalty at all.

Download the Java source: - DatabaseProcedure.java

Note: This class is part of the JHeadstart runtime library and a good example of the power of JHeadstart.