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

  • Spring 3.0.1 StoredProcedure issue

    Hi,

    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:
    java.io.IOException: ORA-22922: nonexistent LOB value
      at oracle.jdbc.driver.DatabaseError.SQLToIOException(DatabaseError.java:517)
      at oracle.jdbc.driver.OracleClobReader.needChars(OracleClobReader.java:183)
      at oracle.jdbc.driver.OracleClobReader.read(OracleClobReader.java:129)
      at java.io.BufferedReader.fill(BufferedReader.java:136)
      at java.io.BufferedReader.readLine(BufferedReader.java:299)
      at java.io.BufferedReader.readLine(BufferedReader.java:362)
    The stored proc class is shown below:

    Code:
    package com.demo;
    
    import java.io.BufferedReader;
    import java.io.IOException;
    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())) {
                            builder.append(currentLine);
                        }
                        
                        resutString = builder.toString();  
                    } finally {
                        if(null != reader) {
                            reader.close();
                        }
                    }
                } 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:
    Code:
    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:
    Code:
    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;
    
    @RunWith(SpringJUnit4ClassRunner.class)
    @ContextConfiguration(locations = {"classpath:spring/test-ApplicationContext.xml"})
    public class DataLookupStoredProcedureTest
    {
      /** Logger */
      private final static Logger logger = LoggerFactory.getLogger(DataLookupStoredProcedureTest.class);
      
      @Autowired
      @Qualifier("lookupSP")
      private DataLookupStoredProcedure lookupSP;
      
      /**
       * Executes before all test cases
       */
      @org.junit.BeforeClass
      public static void beforeAllTests() {
        logger.debug("beforeAllTests : START");
        logger.debug("beforeAllTests : END");
      }
    
      /**
       * Executes after all test cases
       */
      @org.junit.AfterClass
      public static void afterAllTests() {
        logger.debug("afterAllTests : START");
        logger.debug("afterAllTests : END");
      }
      
      @org.junit.Test
      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:
    Code:
    <bean id="sqlDataSource" destroy-method="close" class="org.apache.commons.dbcp.BasicDataSource">
     <property name="driverClassName" value="oracle.jdbc.driver.OracleDriver"/>
     <property name="url" value="jdbc:oracle:thin:@my.host:1802:devdb"/>
     <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>
    
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
     <property name="dataSource" ref="sqlDataSource" />
    </bean>
    
    <!-- LOB Handler for handling CLOB/BLOB data -->
    <bean id="oracleLOBHandler" class="org.springframework.jdbc.support.lob.OracleLobHandler">
     <property name="nativeJdbcExtractor" ref="jbossNativeJdbcExtractor" />
    </bean>
    
    <!-- Native JDBC Extractor for LOB Handler. This is used to get the innermost connection object. -->
    <bean id="jbossNativeJdbcExtractor" class="org.springframework.jdbc.support.nativejdbc.CommonsDbcpNativeJdbcExtractor" />
    
    <!-- Stored Procedures -->
    <bean id="lookupSP" class="com.demo.dao.util.stored_procedure.DataLookupStoredProcedure">
     <constructor-arg>
      <ref bean="sqlDataSource"/>
     </constructor-arg>
    </bean>
    The same stored proc is working perfectly fine with raw CallableStatement. Following code works without an issue (dataSource is injected through Spring).

    Code:
    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) {
          cs.close();
        }
        
        if(null != dbCon) {
          dbCon.close();
        }
      }
    } 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.

    Thanks,
    Niranjan
Working...
X