Spring Stored Procedure CLOB OUT Example

This example shows how to read CLOB data returned from Stored Procedure using Spring.

Step 1: Oracle Stored Procedure Setup

Stored Procedure

create or replace PROCEDURE GETARTICLE 
(
  IN_ARTICLE_ID IN NUMBER,  
  OUT_ARTICLE_NAME OUT VARCHAR2,  
  OUT_ARTICLE_CONTENT OUT CLOB  
) AS 
BEGIN
  SELECT ARTICLE_NAME , ARTICLE_CONTENT
  INTO OUT_ARTICLE_NAME, OUT_ARTICLE_CONTENT
  from  ARTICLES WHERE ARTICLE_ID = IN_ARTICLE_ID;
END GETARTICLE;

Step 2: Implement SqlReturnType

Create a new class that implements org.springframework.jdbc.core.SqlReturnType to read CLOB data and convert it into String object.

CLOBToStringConverter.java

package com.kswaughs.util;

import java.io.IOException;
import java.io.Reader;
import java.sql.CallableStatement;
import java.sql.Clob;
import java.sql.SQLException;

import org.springframework.jdbc.core.SqlReturnType;

public class CLOBToStringConverter implements SqlReturnType {

    @Override
    public Object getTypeValue(CallableStatement cs,
            int paramIndex, int sqlType, String typeName) throws SQLException {
        
         Clob aClob = cs.getClob(paramIndex);

         final Reader clobReader = aClob.getCharacterStream();
         
         int length = (int) aClob.length();
         char[] inputBuffer = new char[1024];
         final StringBuilder outputBuffer = new StringBuilder();
         try {
            while ((length = clobReader.read(inputBuffer)) != -1)
             {
             outputBuffer.append(inputBuffer, 0, length);
             }
        } catch (IOException e) {
            throw new SQLException(e.getMessage());
        }
         return outputBuffer.toString();
    }
}

Step 3: DAO Implementation

Write a DAO class that extends org.springframework.jdbc.object.StoredProcedure. While declaring the OUT parameters in constructor, pass the above CLOBToStringConverter as parameter to SqlOutParameter.

ArticleDAO.java

package com.kswaughs.dao;

import java.sql.Types;
import java.util.HashMap;
import java.util.Map;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.SqlOutParameter;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.object.StoredProcedure;

import com.kswaughs.util.CLOBToStringConverter;

public class ArticleDAO  extends StoredProcedure {
    
    public ArticleDAO(final JdbcTemplate jdbcTemplate) {

        super(jdbcTemplate, "GETARTICLE");
        
        // Input Parameters
        declareParameter(new SqlParameter("IN_ARTICLE_ID", Types.NUMERIC));
        
        // Output Parameters
        declareParameter(new SqlOutParameter("OUT_ARTICLE_NAME", Types.VARCHAR));
        declareParameter(new SqlOutParameter("OUT_ARTICLE_CONTENT", Types.CLOB, 
             null, new CLOBToStringConverter()));
    }
    
    
    public String getArticleContent(final String articleID) throws Exception {
        
        Map returnMap = null;
        
        Map inParams = new HashMap();
        inParams.put("IN_ARTICLE_ID", articleID);
    
        Map returnMap = super.execute(inParams);
        
        if(returnMap== null || returnMap.isEmpty()){
            
            throw new Exception("Article Not found") ;
        }
        
        String articleContent = String.valueOf(returnMap.get("OUT_ARTICLE_CONTENT"));
        return articleContent;
    }    
    
}

Common Mistakes

If we pass typeName as Empty String to SqlOutParameter as below,

 declareParameter(new SqlOutParameter("OUT_ARTICLE_CONTENT", Types.CLOB, 
        "", new CLOBToStringConverter()));

we will get below SQLException

Error occured while executing the Stored procedure. CallableStatementCallback; uncategorized SQLException for SQL [{call GETARTICLE(?, ?, ?)}]; SQL state [99999]; error code [17060]; Fail to construct descriptor: empty Object name; nested exception is java.sql.SQLException: Fail to construct descriptor: empty Object name at com.kswaughs.dao.ArticleDAO.getArticleContent.