A blog about Oracle ADF. This blog is no longer maintained because I left the ADF world for real HTML5 development using technologies like Web Components and frameworks like AngularJS, etc.

Wednesday, December 1, 2010

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).

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:

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.

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. ;-)