Calling stored procedures from within UCM

The Problem
When creating customizations for UCM (Oracle Universal Content Management), there have been a couple of times that I've wanted to call a procedure or function from within a service or filter (ie. to make use autonomous transactions for logging stuff). This post will show you how this can be done (without having to resort to using workarounds like making the procedure into a function and select it from dual).

The procedure
So lets create an interesting - yet compact - procedure that contains in, in-out and out parameters (as this is where the workaround can't be applied anymore). So here it is; my very own example_procedure:

create or replace procedure example_procedure (
   param1 in number,
   param2 in out varchar2,
   param3 out number
) as
begin
   param2 := param2 || param1;
   param3 := param1 + 2;
end example_procedure;

The query (definition)
Now we will define the query we want to call using the Component Wizard (note that you need to use Launch Editor and manually edit the entry because these options can't be selected from a dropdown menu). I am going to call my query exampleQuery and have defined it as:

<tr>
    <td>(ORACLE.CALLABLE)exampleQuery</td>
    <td>{call example_procedure( ?, ?, ? )}</td>
    <td>param1 int
        param2 inout:varchar
        param3 out:int</td>
</tr>

There are several interesting things about this definition that you need to remember:

• There is an (ORACLE.CALLABLE) tag in front of the query name; I believe this prepares the query parser for a procedure/function call.
• The statement itself is between brackets and is preceded by the word call; the syntax to call a procedure or function
• Two parameters have a prefix (inout: and out:); they are pretty self explanatory

The custom Java code 
Finally there is the Java code necessary to call our fancy procedure.

private void testProcedureCall (
   final Workspace workspace,
   final DataBinder binder
) throws DataException {

   // prepare the "in" parameters
   binder.putLocal( "param1", Integer.toString( 33 ) );
   binder.putLocal( "param2", "some test string" );

   // the magical moment; calling the procedure
   final CallableResults myResults = workspace.executeCallable( "exampleQuery", binder );

   // retrieve the "out" parameters
   final String myInOutParameter = myResults.getString( "param2" );
   final int myOutParameter = myResults.getInteger( "param3" );

   // now you can use the output parameters however you like!

}

The above is not that special, except for the executeCallable call. It's use (and arguments) are exactly equal to a createResultSet or execute call on the workspace, except that you get a CallableResults from which you can retrieve the parameters.

And... that should do the trick!