2 Replies Latest reply on Feb 3, 2013 11:01 PM by EJP

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


      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

      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.registerOutParameter(2, oracle.jdbc.OracleTypes.ARRAY, "CSSR.CODE_TABLE");

      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();

      java.sql.Clob clb = (Clob)obj[0];
      String scenario = clb.getSubString(1, (int) clb.length()); //It is taking time to execute this line


      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.