Announcement Announcement Module
No announcement yet.
Spring 3.0.1 StoredProcedure issue Page Title Module
Move Remove Collapse
Conversation Detail Module
  • Filter
  • Time
  • Show
Clear All
new posts

  • Spring 3.0.1 StoredProcedure issue


    We are using Spring JDBC to invoke stored procs from Oracle 10g database. Spring version that we use is 3.0.1. We have a stored proc which returns a CLOB data. The stored procedure class is failing with following exception:

    Code: ORA-22922: nonexistent LOB value
      at oracle.jdbc.driver.DatabaseError.SQLToIOException(
      at oracle.jdbc.driver.OracleClobReader.needChars(
    The stored proc class is shown below:

    package com.demo;
    import java.sql.SQLException;
    import java.sql.Types;
    import java.util.Map;
    import javax.sql.DataSource;
    import org.apache.commons.lang.StringUtils;
    import org.slf4j.Logger;
    import org.slf4j.LoggerFactory;
    import org.springframework.jdbc.core.SqlOutParameter;
    import org.springframework.jdbc.core.SqlParameter;
    import org.springframework.jdbc.object.StoredProcedure;
    import com.demo.util.constants.DAOConstants;
    public class DataLookupStoredProcedure extends StoredProcedure
      /** Logger */
      private final static Logger logger = LoggerFactory.getLogger(DataLookupStoredProcedure.class);
      public static final String STORED_PROC_NAME = "pkg_look_up.p_data_look_up";
       * Constructor to initialize the Stored Proc execution environments.
       * @param dataSource
      public DataLookupStoredProcedure(final DataSource dataSource) {
        super(dataSource, STORED_PROC_NAME);
            // Register IN parameters
            declareParameter(new SqlParameter(DAOConstants.IN_INTERFACE_ID, Types.VARCHAR));
            declareParameter(new SqlParameter(DAOConstants.IN_XML_STRING, Types.VARCHAR));
            // Register OUT parameters
            declareParameter(new SqlOutParameter(DAOConstants.OUT_ERROR_CODE, Types.VARCHAR));
            declareParameter(new SqlOutParameter(DAOConstants.OUT_MESSAGE_DATA, Types.CLOB));
       * Executes procedure and returns a result map.
       * @param inputParamMap
       * @return
      public Map<String, Object> executeProc(final Map<String, Object> inputParamMap) {
        logger.debug("executeProc : START");
        // Execute the proc to get the response
        final Map<String, Object> resultMap = execute(inputParamMap);
        // Convert the CLOB data to String
        if(!resultMap.isEmpty()) {
          final oracle.sql.CLOB clob = (oracle.sql.CLOB) resultMap.get(DAOConstants.OUT_MESSAGE_DATA);
          final String responseXmlString = this.getStringFromClob(clob);
          // Now update the HashMap with this responseString for the key DAOConstants.OUT_MESSAGE_DATA.
          resultMap.put(DAOConstants.OUT_MESSAGE_DATA, responseXmlString);
        logger.debug("executeProc : END");
        return resultMap;
         * Converts the java.sql.Clob data to String.
         * @param clob
         * @return
        private String getStringFromClob(final oracle.sql.CLOB clob) {
            logger.debug("getStringFromClob : START");
            String resutString = StringUtils.EMPTY;
            if(null != clob) {
                StringBuilder builder = new StringBuilder();
                BufferedReader reader = null;
                logger.debug("Clob Length --> {}", clob.getLength());
                try {
                    try {
                        reader = new BufferedReader(clob.getCharacterStream());
                        String currentLine = null;
                        while(null != (currentLine = reader.readLine())) {
                        resutString = builder.toString();  
                    } finally {
                        if(null != reader) {
                } catch(IOException e) {
                  logger.error("IOException in converting Clob to String. {}", e.getMessage());
                    logger.debug("IOException in converting Clob to String. {}", e);
                } catch(SQLException sqle) {
                  logger.error("SQLException in converting Clob to a String. {}", sqle.getMessage());
                    logger.debug("SQLException in converting Clob to a String. {}", sqle);
            logger.debug("getStringFromClob : END");
            return resutString;
    The stored proc signature is shown below:
    create or replace package pkg_look_up AS
      PROCEDURE p_data_look_up
        in_interface_id      IN      varchar2,
        in_xml_string       IN       varchar2,
        out_error_code      OUT      varchar2,
        out_message_data    OUT     clob
    END pkg_look_up;
    The Java class to test this is shown below:
    package com.demo.test;
    import java.util.HashMap;
    import java.util.Map;
    import org.junit.runner.RunWith;
    import org.slf4j.Logger;
    import org.slf4j.LoggerFactory;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.beans.factory.annotation.Qualifier;
    import org.springframework.test.context.ContextConfiguration;
    import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
    import com.demo.util.constants.DAOConstants;
    import com.demo.dao.util.stored_procedure.DataLookupStoredProcedure;
    @ContextConfiguration(locations = {"classpath:spring/test-ApplicationContext.xml"})
    public class DataLookupStoredProcedureTest
      /** Logger */
      private final static Logger logger = LoggerFactory.getLogger(DataLookupStoredProcedureTest.class);
      private DataLookupStoredProcedure lookupSP;
       * Executes before all test cases
      public static void beforeAllTests() {
        logger.debug("beforeAllTests : START");
        logger.debug("beforeAllTests : END");
       * Executes after all test cases
      public static void afterAllTests() {
        logger.debug("afterAllTests : START");
        logger.debug("afterAllTests : END");
      public void getResponseXml() {
        logger.debug("getResponseXml : START");
        String xmlString = "sample xml";
        final Map<String, Object> inParamMap = new HashMap<String, Object>(2);
        inParamMap.put(DAOConstants.IN_INTERFACE_ID, "SERNBRLOOKUP");
        inParamMap.put(DAOConstants.IN_XML_STRING, xmlString);
        final Map<String, Object> resultMap = lookupSP.executeProc(inParamMap);
        logger.debug("Error code --> {}", resultMap.get(DAOConstants.OUT_ERROR_CODE));
        logger.debug("Message Data --> {}", resultMap.get(DAOConstants.OUT_MESSAGE_DATA));
        logger.debug("getResponseXml : END");
    Spring configuration is shown below:
    <bean id="sqlDataSource" destroy-method="close" class="org.apache.commons.dbcp.BasicDataSource">
     <property name="driverClassName" value="oracle.jdbc.driver.OracleDriver"/>
     <property name="url" value=""/>
     <property name="username" value="user" />
     <property name="password" value="password" />
     <property name="initialSize" value="2" />
     <property name="maxActive" value="2" />
     <property name="maxIdle" value="2" />
     <property name="minIdle" value="0" />
     <property name="defaultAutoCommit" value="false" />
     <property name="accessToUnderlyingConnectionAllowed" value="true" />
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
     <property name="dataSource" ref="sqlDataSource" />
    <!-- LOB Handler for handling CLOB/BLOB data -->
    <bean id="oracleLOBHandler" class="">
     <property name="nativeJdbcExtractor" ref="jbossNativeJdbcExtractor" />
    <!-- Native JDBC Extractor for LOB Handler. This is used to get the innermost connection object. -->
    <bean id="jbossNativeJdbcExtractor" class="" />
    <!-- Stored Procedures -->
    <bean id="lookupSP" class="com.demo.dao.util.stored_procedure.DataLookupStoredProcedure">
      <ref bean="sqlDataSource"/>
    The same stored proc is working perfectly fine with raw CallableStatement. Following code works without an issue (dataSource is injected through Spring).

    try {
      CallableStatement cs = null;
      Connection dbCon = null;
      try {
        dbCon = dataSource.getConnection();
        cs = dbCon.prepareCall(sql);
        cs.registerOutParameter(3, Types.VARCHAR);
        cs.registerOutParameter(4, Types.CLOB);
        cs.setString(1, interfaceId);
        cs.setString(2, requestXml);
        final boolean isSuccess = cs.execute();
        logger.debug("Executed successfully? {}", isSuccess);
        logger.debug("Error code --> {}", cs.getString(3));
        logger.debug("Message data --> {}", cs.getString(4));
      } finally {
        if(null != cs) {
        if(null != dbCon) {
    } catch(Exception e) {
      logger.error("Exception in executing proc --> {}", e);
    Could someone help me to find out if this is an issue with Spring JDBC or JDBC driver.