Java ArrayList, Callable Statements and PL/SQL Procedures

If you’re developing a Java Application which integrates with an Oracle back-end, you’ve probably run into the following technicalities:
transforming an arraylist as a parameter to a callable statement to process and persist the data in your Oracle Database.

How can you accomplish this in a generic and re-usable manner:
1. Create an objecttype (PL/SQL) – Backend:

CREATE OR REPLACE type ot_emp as object
( emp_id number(10),
emp_cd varchar2(20),
name varchar2(100));

2. Create a collection-type based on the objecttype (PL/SQL) – Backend:

CREATE OR REPLACE type ct_emp as table of ot_emp;

3. Create a PL/SQL procedure to process and persist the data coming from the front-end app:

PROCEDURE p_insert_emp(
pi_employees IN ct_emp_coll,
po_message_info OUT ot_message_info);

4. Create a Java Object for each Oracle object-type implementing the SQLData-API:

public class Employee extends SuperEmployee implements SQLData
public void readSQL(SQLInput stream, String typeName) {}
public String
getSQLTypeName() { return
“HR.OT_EMP”; }
public void writeSQL(SQLOutput stream)
throws SQLException {

The readSQL() method is used to map the Oracle data to Java data, the getSQLTypeName() and writeSQL() is used to map java data to Oracle data.

After putting together the framework, you can then pass the arraylist with the callablestatement and process the information into your back-end environment.

Of course this kind of functionality is handled by ORM frameworks as well, such as Hibernate, Toplink, iBatis, …