Thursday, May 28, 2009

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");
}
}
}

19 comments:

Kerry Wilson said...

Not using the Spring JDBCTemplate methods for calling a stored procedure?

BTW, I recommend Syntax highlighter for code.

http://code.google.com/p/syntaxhighlighter/

I use it on my blog:

http://www.goodercode.com

Деян Дичев 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 Saha said...

Kerry,
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

andrews selvaraj said...

Hi

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

Please Help.

Marco Serrano said...

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!

banuchander said...

Thank you for this post!!!
I was able to fix problem in my app.

AllwynPlanet said...

Hi,

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
************weblogic.jdbc.rmi.SerialConnection_weblogic_jdbc_rmi_internal_ConnectionImpl_weblogic_jdbc_wrapper_PoolConnection_oracle_jdbc_driver_T4CConnection_1000_WLStub@1
aFTER cALLABLE sTATEMENT
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(TypeDescriptor.java:309)
at oracle.sql.TypeDescriptor.(TypeDescriptor.java:129)
at oracle.sql.ArrayDescriptor.(ArrayDescriptor.java:268)
at oracle.sql.ArrayDescriptor.createDescriptor(ArrayDescriptor.java:196)
at oracle.sql.ArrayDescriptor.createDescriptor(ArrayDescriptor.java:165)
at oracle.sql.ArrayDescriptor.createDescriptor(ArrayDescriptor.java:150)
at oracle.sql.ArrayDescriptor.createDescriptor(ArrayDescriptor.java:115)
at co.ba.cl.fm.VarrayDemo.main(VarrayDemo.java:72)
Caused by: java.rmi.MarshalException: error marshalling return; nested exception is:
java.io.NotSerializableException: oracle.jdbc.driver.T4CConnection
at weblogic.rjvm.ResponseImpl.unmarshalReturn(ResponseImpl.java:221)
at weblogic.rmi.internal.BasicRemoteRef.invoke(BasicRemoteRef.java:224)
... 10 more
Caused by: java.io.NotSerializableException: oracle.jdbc.driver.T4CConnection
at java.io.ObjectOutputStream.writeObject0(ObjectOutputStream.java:1075)
at java.io.ObjectOutputStream.writeObject(ObjectOutputStream.java:291)
at weblogic.rjvm.MsgAbbrevOutputStream.writeObject(MsgAbbrevOutputStream.java:614)
at weblogic.utils.io.ChunkedObjectOutputStream.writeObject(ChunkedObjectOutputStream.java:63)
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(BasicServerRef.java:589)
at weblogic.rmi.internal.BasicServerRef$1.run(BasicServerRef.java:479)
at weblogic.security.acl.internal.AuthenticatedSubject.doAs(AuthenticatedSubject.java:363)
at weblogic.security.service.SecurityManager.runAs(Unknown Source)
at weblogic.rmi.internal.BasicServerRef.handleRequest(BasicServerRef.java:475)
at weblogic.rmi.internal.BasicServerRef.access$300(BasicServerRef.java:59)
at weblogic.rmi.internal.BasicServerRef$BasicExecuteRequest.run(BasicServerRef.java:1016)
at weblogic.work.ExecuteThread.execute(ExecuteThread.java:200)
at weblogic.work.ExecuteThread.run(ExecuteThread.java:172)

Anonymous said...

Interesting post but don't understand this line :
TableTest ob = records.get(j);

What is TableTest ?

Sandeep Krishna M said...

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
n.java:121)
at jsp_servlet.__testconnection._jspService(__testconnection.java:243)
at weblogic.servlet.jsp.JspBase.service(JspBase.java:34)
at weblogic.servlet.internal.StubSecurityHelper$ServletServiceAction.run
(StubSecurityHelper.java:227)
at weblogic.servlet.internal.StubSecurityHelper.invokeServlet(StubSecuri
tyHelper.java:125)
at weblogic.servlet.internal.ServletStubImpl.execute(ServletStubImpl.jav
a:300)
at weblogic.servlet.internal.ServletStubImpl.onAddToMapException(Servlet
StubImpl.java:416)
at weblogic.servlet.internal.ServletStubImpl.execute(ServletStubImpl.jav
a:326)
at weblogic.servlet.internal.ServletStubImpl.execute(ServletStubImpl.jav
a:183)
at weblogic.servlet.internal.WebAppServletContext$ServletInvocationActio
n.doIt(WebAppServletContext.java:3686)
at weblogic.servlet.internal.WebAppServletContext$ServletInvocationActio
n.run(WebAppServletContext.java:3650)
at weblogic.security.acl.internal.AuthenticatedSubject.doAs(Authenticate
dSubject.java:321)
at weblogic.security.service.SecurityManager.runAs(SecurityManager.java:
121)
at weblogic.servlet.internal.WebAppServletContext.securedExecute(WebAppS
ervletContext.java:2268)
at weblogic.servlet.internal.WebAppServletContext.execute(WebAppServletC
ontext.java:2174)
at weblogic.servlet.internal.ServletRequestImpl.run(ServletRequestImpl.j
ava:1446)
at weblogic.work.ExecuteThread.execute(ExecuteThread.java:201)
at weblogic.work.ExecuteThread.run(ExecuteThread.java:173)

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.