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.

15 comments:

  1. Paco,
    Maybe you kow. Does this also work with a stored procedure that returns boolean ? I'm currently struggling with calling such a proc. proc (p_val in varchar2, p_succes out boolean)

    ..............................
    String stmt = "begin proc(?,?); end;";
    st = getDBTransaction().createCallableStatement(stmt, 0);
    st.registerOutParameter(1, Types.BOOLEAN);
    st.setObject(1, "inputval");
    st.executeUpdate();
    ..............................
    As soon as "registerOutParameter" is called I get an "Invalid columnType: 16" where 16 refers to Types.BOOLEAN. Looks like BOOLEAN is not supported ? (ADF 11.1.1.3)

    ReplyDelete
  2. Hi Luc,

    I'm afraid that the Oracle JDBC drivers don't support BOOLEAN. See: http://www.oracle.com/technetwork/database/enterprise-edition/jdbc-faq-090281.html#34_05

    They make it sound like there is a good reason for it, so it is no use waiting for it to appear in a future release. :-(

    Workaround is (obviously) to use a NUMBER or a CHAR instead of the BOOLEAN.

    Paco.

    ReplyDelete
  3. By the way, why do you use executeUpdate()?

    ReplyDelete
  4. I added a Performance section after some questions about the performance of the class.

    ReplyDelete
  5. Hello, Paco!
    What if I wish just to execute simple select like 'select sysdate from dual'? Is there in JHeadStart something such useful like DatabaseProcedure to do this? Maybe DatabaseQuery class :)?

    Vova

    ReplyDelete
  6. Vova,

    Currently, JHeadstart only supports ADF Business Components as the Business Services layer. In ADF BC one would use ViewObjects to query data from the database. Because of this, there was no need to create a DatabaseQuery class. :)

    I guess you don't use ADF?

    Paco

    ReplyDelete
  7. Thank you, Paco.
    You aren’t right. We are using ADF and ViewObjects for big queries. There are more than hundred of them in our project and much more instances. I don’t think it is a good idea to create one more hundred for simple queries. And one more reason not to use for this ViewObjects is that because of there are other relative projects developing for the same database, its structure can be changed being adapted for new requirements. So, for simple queries we’re starting to use mechanism when we store them in database to do our application more independent from database structure. So, because we can’t change SQL in ViewObject programmatically, there is need in some helping class to execute these small queries. And if there is nothing for this in JHeadStart, we will implement it by ourselves :)
    Vova

    ReplyDelete
  8. OK, I see. Perhaps you could create a shared ADF library with an Application Module that provides these simple queries. This can then be reused as a nested AppModule in other projects.

    Anyway, I think it is not hard to create a DatabaseQuery class based on the DatabaseProcedure class. If you do, could you blog about it and send me the link? :-D

    Paco

    ReplyDelete
  9. Hi Paco,

    The link for download your class is broken.

    Could you please re add your class ?

    Thank you very much!

    Patrick

    ReplyDelete
  10. Hi Patrick,

    It should work again (using Pastebin this time).

    Regards,
    Paco

    ReplyDelete
  11. The link now points to a new version of this class. I have fixed a problem with getOracleOutputValue(). I didn't return useful data before. Now it does.

    ReplyDelete
  12. Hi Paco. Nice class... But I'm having an issue with it.
    Our database is putting all the errors that occured in procedures (PL/SQL obviously) on a stack, so later on, we can call a function that will return user friendly messages. Now we are migrating to JHeadStart and we want to take advantage of that feature. The problem is that the method "public Result call(DBTransaction transaction, Object... parameters)" does NOT throw SQLException like method "public Result call(Connection connection, Object... parameters) throws SQLException".
    I would like to do the following:
    try {
    // Call PL/SQL procedure using DatabaseProcedure.call with my transaction
    }
    catch (SQLException e) {
    DatabaseProcedure f_return_error = DatabaseProcedure.define("function f_return_error return varchar2");
    String error_msg = (String)f_return_error.call(transaction).getReturnValue();
    throw new JboException(error_msg);
    }

    My error handler would then handle errors and display it in user friendly manner. Do you have any suggestions how I could achieve that using your class DatabaseProcedure? I cannot override your class and replace call method right? Can I copy it and change it or do you have any copyrights restrictions? My only modification to "public Result call(DBTransaction transaction, Object... parameters)" would be to append "throws SQLException". Please consider it.
    Thank you for any useful info. I really appreciate your work.
    Regards, Marko

    ReplyDelete
  13. Hi Marko,

    As you can see in the source, all SQLExceptions are caught and wrapped in a SQLStmtException (which is a JBOException if I remember correctly). So it is possible to create a subclass that unwraps this Exception. Will that work for you?

    catch (SQLException e) {
    sLog.severe("Failed to execute statement", e);
    throw new SQLStmtException(CSMessageBundle.class, CSMessageBundle.EXC_SQL_EXECUTE_COMMAND, getEscapeSequence(), e);
    }

    I'm not sure what copyright restrictions apply to this source code. But I guess (I really hope) that you cannot copyright regular expressions and these are actually the most important part of this class. So, feel free to create your own class based on the same idea (and the same regular expressions).

    Regards,
    Paco

    ReplyDelete
  14. PS: If I have time I will add an extra call method with checked exceptions (SQLException).

    ReplyDelete