2 Replies Latest reply: Feb 3, 2013 5:01 PM by EJP RSS

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

    982834

      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.