Passing oracle.sql.ARRAY to a Stored Procedure

The scope of this post is to share my experience to pass an oracle ARRAY to a stored procedure. It was a bit painful exercise and I will be happy if it can help my fellow developers.

Our objective was to do a bulk insert into Oracle table. We are using spring/hibernate configuration to persist in oracle. But there was a situation where we need to persist millions of records at one shot and hibernate was bit slow for that. So, our DBA wrote a stored procedure to do a bulk insert.

But we need to populate the oracle ARRAY with all records to be inserted using java code. I faced two issues

1. Getting native oracle connection from a wrapped connection. In general, the application server (Jboss, Geronimo for example) or web server (like Tomcat) uses javax.sql.DataSource interface to wrap the native database connection.
   
2. Constructing two dimensional ARRAY and STRUCT
   

Oracle Related stuff

Suppose, we have a database table called TABLE_TEST

CREATE TABLE "Schema"."TABLE_TEST"
( "ID" NUMBER(10,0) NOT NULL,
"NAME" VARCHAR2 NOT NUL,
"ATTRIBUTE1" VARCHAR2,
"ATTRIBUTE2" VARCHAR2,
CONSTRAINT "PK_ TABLE_TEST" PRIMARY KEY ("ID")
)

Create a type

create or replace type T_TYPE is object (
NAME VARCHAR2(100),
ATTRIBUTE1 VARCHAR2(100),
ATTRIBUTE2 VARCHAR2(100)
)

Create a type TB_T_TYPE of type T_TYPE
create or replace type TB_T_TYPE is table of T_TYPE;

Stored procedure
It generate the Id from the sequence

create or replace procedure POPULATE_ TABLE_TEST (example TB_T_TYPE) as
type t_tb_seq is table of number;
l_t_seq t_tb_seq := t_tb_seq();
begin
for i in 1.. example.count loop
l_t_seq.extend;
select s_ TABLE_TEST.nextval into l_t_seq(i) from dual;
end loop;
forall i in 1.. example.count
insert into TABLE_TEST(id,name,attribute1,attribute2)
values (l_t_seq(i),
treat(example(i) as T_TYPE).NAME,
treat(example(i) as T_TYPE).ATTRIBUTE1,
treat(example(i) as T_TYPE).ATTRIBUTE2
);
end;

Java code

applicationContext

 <org.springframework.jdbc.core.JdbcTemplate">

 

OracleStoredProcedure

import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
import oracle.sql.STRUCT;
import oracle.sql.StructDescriptor;

public class OracleStoredProcedure {
private JdbcTemplate jdbcTemplate;
private final String ORACLE_STRUCT = "T_TYPE ";
private final String ORACLE_ARRAY = "TB_T_TYPE";
private static Log log = LogFactory.getLog(OracleStoredProcedure.class);

public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}


public void insertAll(List records) throws DaoException {
Connection conn=null;
StructDescriptor structDescriptor=null;
ArrayDescriptor arrayDescriptor=null;
int iSize = records.size();
Object[] arrObj =null;
Object[][] recObj =null;
try {
//For Tomcat/JBoss
conn=jdbcTemplate.getDataSource().getConnection();
log.debug("got connection from jdbcTemplate ");
structDescriptor = StructDescriptor.createDescriptor(ORACLE_STRUCT, conn.getMetaData().getConnection());
arrayDescriptor = ArrayDescriptor.createDescriptor(ORACLE_ARRAY, conn.getMetaData().getConnection());

arrObj = new Object[iSize];
recObj = new Object[iSize][5];

//Structuring obj and arrays

for (int j = 0; j < iSize ;j++){

        TableTest ob= records.get(j);

        recObj[j][0]=ob.getName();

        recObj[j][1]=ob.getAttribute1();

        recObj[j][2]=ob.getAttribute2();

       

        arrObj[j] = new STRUCT(structDescriptor, conn.getMetaData().getConnection(), recObj[j]);

   }

       ARRAY arr = new ARRAY(arrayDescriptor, conn.getMetaData().getConnection(), recObj);      

       PreparedStatement preparedStatement=conn.prepareStatement("{call POPULATE_ TABLE_TEST (?)}");

       preparedStatement.setArray(1, arr);

       preparedStatement.execute();

       log.debug("Stored procedure POPULATE_ TABLE_TEST is executed");

}catch (Exception e) {

throw new DaoException("Error while doing bulk insert using POPULATE_ TABLE_TEST Stored procedure", e);

} finally{

try {

       if (conn != null){ 

        conn.close();

        log.debug("connection from jdbcTemplate is closed");

       }

} catch (SQLException e2) {

log.debug("problem while closing connection");

}

}

}