Calling a database procedure with table input and output parameters from Java

In my previous blog post I explained how to call a database procedure using only a few lines of Java code. I already hinted that it is possible to use custom types and tables, etc. Let's see an example of that in more detail.

Database artifacts

Suppose we have the following database artifacts (in an Oracle XE HR scheme).

An object type department_rec:

create or replace type department_rec as object
  ( id number(4,0)
  , name varchar2(30)
  )

A table type department_tab:

create or replace type department_tab as table of department_rec

And a procedure with a table in- and output parameter that looks up the names of departments for the ids in the provided table:

create or replace
procedure check_deps
  ( p_deps in out department_tab )
is
begin
  if p_deps is null or p_deps.count = 0
  then
    return;
  end if;
  
  for i in p_deps.first..p_deps.last
  loop
    begin
      select department_name into p_deps(i).name
      from departments
      where department_id = p_deps(i).id;
    exception
      when no_data_found then
        null; -- Just skip it.
    end;
  end loop;
end;

Registering a custom type

To use the department_tab type in our calls, we need to register it with DatabaseProcedure before the static initialization. I created an ApplicationModule and added the following code to the implementation class.

static
{
  // We have to register the DEPARTMENT_TAB type as an Array Type (PL/SQL
  // "table of ..." types become Arrays in Java). Because we have registered
  // the type, we can use it in any DatabaseProcedure definition from now on.
  // Note that this is case-sensitive and should match the case that is used
  // in the database (upper-case by default).
  DatabaseProcedure.registerArrayType("DEPARTMENT_TAB");
}

// The same as before. We simply copy the signature of the procedure. Note
// that we use the custom type "department_tab" here.
private static final DatabaseProcedure CHECK_DEPS =
  DatabaseProcedure.define("procedure check_deps ( p_deps in out department_tab )");

The actual call in an ApplicationModule implementation class

As we saw in the previous post, we can call the procedure like this in an AppModuleImpl class:

public void test()
{
  Object depsIn = null; // Our input...

  DatabaseProcedure.Result result = CHECK_DEPS.call(getDBTransaction(), depsIn);

  Object depsOut = result.getOutputValue("p_deps");
}

But how do we create a department_tab type in Java?

Using ADF BC Domains

We don't need to use Business Components, but it makes our life really easy. The first thing we do is create a new Domain for the department_rec type. It automatically detects the type and initializes with the right attributes.

This domain gives us a Java class (DepartmentRec.java) for free, we will use that to create the input for the call. We can provide a simple Java array (DepartmentRec[]) to the DatabaseProcedure.call() method. It will automatically be converted to the correct type.

That is all we need to create the final method:

public void test()
{
  // We create some dummy data.
  DepartmentRec[] depsIn = new DepartmentRec[]
    { createDepartmentRec(50, "Dep50"), createDepartmentRec(20, "Dep20"),
      createDepartmentRec(10, "Dep10"), createDepartmentRec(55, "Not existing!") };

  DatabaseProcedure.Result result = CHECK_DEPS.call(getDBTransaction(), (Object) depsIn);
  // Note that the cast to Object is needed to instruct the Java compiler how to use
  // the varargs feature. Without the cast the call will fail.

  // The result object provides two methods to get the output of the call:
  // - getOutputValue()       returns a   java.sql.Array           object
  // - getOracleOutputValue() returns an  oracle.jbo.domain.Array  object

  // We will use the oracle.jbo.domain.Array.
  Array array = (Array) result.getOracleOutputValue("p_deps");
  for (Object elem: array.getArray())
  {
    DepartmentRec dep = createDepartmentRec((Datum) elem);

    System.out.println(dep.getId() + ": " + dep.getName());
  }
}

I used two convenience methods:

private DepartmentRec createDepartmentRec(int id, String name)
{
  try
  {
    DepartmentRec dep = new DepartmentRec();
    dep.setId(new oracle.jbo.domain.Number(id));
    dep.setName(name);
    return dep;
  }
  catch (SQLException sqle)
  {
    throw new RuntimeException(sqle);
  }
}

private DepartmentRec createDepartmentRec(Datum o)
{
  try
  {
    return new DepartmentRec(o);
  }
  catch (SQLException sqle)
  {
    throw new RuntimeException(sqle);
  }
}

And that's it! Just add the test() method to the Client Interface of the ApplicationModule and run it. The output in the console is:

50: Shipping
20: Marketing
10: Administration
55: Not existing!

Not using ADF BC Domains

The DepartmentRec is a subclass of oracle.jbo.domain.Struct. It provides us with type-safety and convenience methods to get and set attributes. You can however simply use the Struct class if you don't use ADF BC or if you just like to make things complicated. ;-)