Skip to main content

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 a type

create or replace type T_TYPE is object (

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();
for i in 1.. example.count loop
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

Java code




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
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);
        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);
       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){ 
        log.debug("connection from jdbcTemplate is closed");
} catch (SQLException e2) {
log.debug("problem while closing connection");


Kerry Wilson said…
Not using the Spring JDBCTemplate methods for calling a stored procedure?

BTW, I recommend Syntax highlighter for code.

I use it on my blog:
Deyan Dichev said…
Thank you very much for the example.
It was very useful for me.
I just want to mention that I had a character set exception

java.sql.SQLException: Non supported character set: oracle-character-set-171.
This happened when I try to instance a new STRUCT

However, when I update my ojdbc driver(new jars are dms.jar,ocrs12.jar,ojdbc14dms.jar ojdl.jar,orai18n.jar) the problem was fixed.
Anil Kumar Saha said…
I appreciate your comments and thanks for the link to highlighting codes. I new to Spring jdbc.I'm sure it can be done using spring jdbc also. Actually,the oracle native connections drive me like nuts and I had to achieve it within a fiexd time.

Деян Дичев,
It is nice to see that my post helped you. Thanks for using it

I am getting compilation error on the below line. TableTest
TableTest ob= records.get(j);

Please Help.
I was somewhat confused with your code, because he did not understand this line:

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

which does nothing. I tried without this line and it works perfect. So no need to define the struct but pass the array T_TYPE type structure. I also noticed that in defining recObj = new Object [isize] [5], the 5 must match the exact number of fields in T_TYPE. I really commend him for his magnificent simple code!
Unknown said…
Thank you for this post!!!
I was able to fix problem in my app.
AllwynPlanet said…

Could you please advice on this. I have same code working when I am using Type-I driver, however, if I am using Type-IV driver i.e. using datasource connection (oracle thin driver) I am hitting with exception:
Before getting look
After getting look
Today = 20-Aug-2010
Before Array Descriptor ...
Unexpected Exception
weblogic.rmi.extensions.RemoteRuntimeException: Unexpected Exception
at weblogic.jdbc.rmi.internal.ConnectionImpl_weblogic_jdbc_wrapper_PoolConnection_oracle_jdbc_driver_T4CConnection_1000_WLStub.physicalConnectionWithin(Unknown Source)
at weblogic.jdbc.rmi.SerialConnection_weblogic_jdbc_rmi_internal_ConnectionImpl_weblogic_jdbc_wrapper_PoolConnection_oracle_jdbc_driver_T4CConnection_1000_WLStub.physicalConnectionWithin(Unknown Source)
at oracle.sql.TypeDescriptor.setPhysicalConnectionOf(
at oracle.sql.TypeDescriptor.(
at oracle.sql.ArrayDescriptor.(
at oracle.sql.ArrayDescriptor.createDescriptor(
at oracle.sql.ArrayDescriptor.createDescriptor(
at oracle.sql.ArrayDescriptor.createDescriptor(
at oracle.sql.ArrayDescriptor.createDescriptor(
Caused by: java.rmi.MarshalException: error marshalling return; nested exception is: oracle.jdbc.driver.T4CConnection
at weblogic.rjvm.ResponseImpl.unmarshalReturn(
at weblogic.rmi.internal.BasicRemoteRef.invoke(
... 10 more
Caused by: oracle.jdbc.driver.T4CConnection
at weblogic.rjvm.MsgAbbrevOutputStream.writeObject(
at weblogic.jdbc.rmi.internal.ConnectionImpl_weblogic_jdbc_wrapper_PoolConnection_oracle_jdbc_driver_T4CConnection_WLSkel.internalInvoke1(Unknown Source)
at weblogic.jdbc.rmi.internal.ConnectionImpl_weblogic_jdbc_wrapper_PoolConnection_oracle_jdbc_driver_T4CConnection_WLSkel.invoke(Unknown Source)
at weblogic.rmi.internal.BasicServerRef.invoke(
at weblogic.rmi.internal.BasicServerRef$
at Source)
at weblogic.rmi.internal.BasicServerRef.handleRequest(
at weblogic.rmi.internal.BasicServerRef.access$300(
at weblogic.rmi.internal.BasicServerRef$
Anonymous said…
Interesting post but don't understand this line :
TableTest ob = records.get(j);

What is TableTest ?
oracle.sql.ARRAY arrayDesc =((OracleCallableStatement) cs).getARRAY(1);

Object[] rowValues = (Object[]) arrayDesc.getOracleArray();

am getting folloing error in the above line can any one help me.

java.lang.ClassCastException: weblogic.jdbc.wrapper.Array_oracle_sql_ARRAY canno
t be cast to oracle.sql.ARRAY
at weblogic.jdbc.wrapper.CallableStatement_oracle_jdbc_driver_OracleCall
ableStatementWrapper.getARRAY(Unknown Source)
at jsp_servlet.__testconnection.testDataSourceConnection(__testconnectio
at jsp_servlet.__testconnection._jspService(
at weblogic.servlet.jsp.JspBase.service(
at weblogic.servlet.internal.StubSecurityHelper$
at weblogic.servlet.internal.StubSecurityHelper.invokeServlet(StubSecuri
at weblogic.servlet.internal.ServletStubImpl.execute(ServletStubImpl.jav
at weblogic.servlet.internal.ServletStubImpl.onAddToMapException(Servlet
at weblogic.servlet.internal.ServletStubImpl.execute(ServletStubImpl.jav
at weblogic.servlet.internal.ServletStubImpl.execute(ServletStubImpl.jav
at weblogic.servlet.internal.WebAppServletContext$ServletInvocationActio
at weblogic.servlet.internal.WebAppServletContext$ServletInvocationActio
at weblogic.servlet.internal.WebAppServletContext.securedExecute(WebAppS
at weblogic.servlet.internal.WebAppServletContext.execute(WebAppServletC
jorge luis said…
Hoy errror en TableTest me You can Ayudar Por Favor tambien me la venta de error en Oracle TB_T_TYPE , help
jorge luis said…
me sale error en procedimiento
Error(2,43): PLS-00201: el identificador 'TB_T_TYPE' se debe declarar
Anonymous said…
java.sql.SQLException: Inconsistent java and sql object types

I am getting above error
Anonymous said…
Thanks a lot for the help.Wasted a lot of time and effort until i found your page.
Chaitali Sanghavi
Anonymous said…
Hi Thanx,
nice post,
i am facing a problem with STRUCT. if, i am tring to pass String in varchar2 field. it is showing null or blank. it never show correct data.
Aneel said…
Good clean post. Helped me. Thanks.
Anil said…
I am getting the below error when creating the arrayDescriptor
arrayDescriptor = ArrayDescriptor.createDescriptor(CUST_TABLE_TYPE, con);

while StructDescriptor is succesful

Could you please help

java.sql.SQLException: invalid name pattern: WEB_USER.cust_type_table_type
Anonymous said…
Thank you very very much, Anil Saha!
Your post was very useful for me.
Anonymous said…
Thank You very much man
It was really helpful.
Anonymous said…
Thank you very much.
It was really helpful.
Anonymous said…
This will not work in multithreaded environment under even medium load. Oracle Arrays are live objects
connection to create Arrays/Structs assumes that you will use SAME connection later.
Since you are using Spring to manage this initial connection, Spring can return connection to the pool in the middle of the function, because some other thread was using same connection and closed it.

Result will be most likely a deadlock. Faced this issue in one of our projects
gibffe said…
ARRAY arr = new ARRAY(arrayDescriptor, conn.getMetaData().getConnection(), recObj);

didn't you want to pass arrObj instead of recObj as last param above ? otherwise it's just a pointless variable

Popular posts from this blog