This discussion is archived
2 Replies Latest reply: Feb 3, 2013 3:01 PM by EJP RSS

CLOB to String conversion is slow while trying to fetch from plsql Type obj

982834 Newbie
Currently Being Moderated
CLOB to String conversion is very slow while trying to fetch from plsql Type Object whereas it takes less time when trying fetch directly from resultset.

We are using jdk 1.6, Oracle 11g, Websphere 7.0, ojdbc6.jar


PLSQL Code:

create or replace type code_table as table of code_rec

create or replace TYPE code_rec as object ( rec_text_href CLOB, rec_source_code varchar2(10), rec_description varchar2(300));


Java Code:

java.sql.Connection con = Broker.getInstance().getConnection(); //JNDI Connection

java.sql.Connection oracleCon oracleCon=((oracle.jdbc.OracleConnection)com.ibm.ws.rsadapter.jdbc.WSJdbcUtil.getNativeConnection
((com.ibm.ws.rsadapter.jdbc.WSJdbcConnection)con));

oracle.sql.ArrayDescriptor codeDescriptor = ArrayDescriptor.createDescriptor("CSSR.DIAG_CODE",oracleCon);
oracle.sql.ARRAY codesArray = new ARRAY(codeDescriptor, oracleCon, vo.getCodesList().toArray());

java.sql.CallableStatement cstmt = con.prepareCall("{CALL CSSR.FUPLOAD(?,?)}");
cstmt.setObject(1,codesArray);
cstmt.registerOutParameter(2, oracle.jdbc.OracleTypes.ARRAY, "CSSR.CODE_TABLE");
cstmt.execute();

oracle.sql.ARRAY outputArray = (ARRAY) cstmt.getObject(2);
oracle.sql.Datum[] datumArray = outputArray.getOracleArray();

for(int j=0;j<datumArray.length;j++){
DataVo voObj=new DataVo();

oracle.sql.STRUCT sqlObj = (STRUCT) datumArray[j];
Object[] obj = sqlObj.getAttributes();

if(obj[3]!=null){         
java.sql.Clob clb = (Clob)obj[0];
String scenario = clb.getSubString(1, (int) clb.length()); //It is taking time to execute this line
}
voObj.setScenario(scenario);

resultsList.add(voObj);
}

Kindly provide any suggestions on this and also let me know if any other alternative approach is available to retrieve plsql array of objects in java.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points