Java Tip: post Clob data to stored procedure in Oracle with Spring Framework

Currently I am very busy with a new Java webapp project. One of the specifiactions was to collect XML data and store it via an Stored Procedure in a Oracle table. The XML data can be very large so we should use CLOB (Character Large Object ). For details, see http://en.wikipedia.org/wiki/CLOB.

The customer is using for all there Java web applications JBoss as the Java Server. As a framework, they have chosen for Spring Framework. It is not bad as a framework, you will get lots of usefull scaffolding, but as almost every framework there are also some annoying parts.

One of the annoying parts is the Oracle Connections, Spring is using lots of wrapper classes around the native Oracle Connections.

After some trial and error I found a fairly simple solution.

In the ApplicationContext.xml, the place where the beans for the webapp are specified, I specified 2 new beans. One of the beans points to the nativeJdbcExtractor. In the oracleLOBHandler I make reference to the other 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” />

In the end my DAO class looks a little bit like below ( I removed customer related code and the imports )

public class UpdateRecordStoredProcedure extends StoredProcedure

private final String storedProcName = “pck_object.verwerk_clob”;

@Autowired

private JdbcTemplate jdbcTemplate;

public UpdateCarsStoredProcedure(final DataSource dataSource) {

declareParameter(new SqlParameter(“p_clob_bericht”, Types.CLOB));

declareParameter(new SqlOutParameter(“p_err_msg”, Types.VARCHAR));

setFunction(false);

setSql(storedProcName);

setDataSource(dataSource);

}

public String updateRecord(String xml){

String error = “no error”;

OracleLobHandler lh = new OracleLobHandler();

lh.setNativeJdbcExtractor(new SimpleNativeJdbcExtractor());

Map<String, Object> params = new HashMap<String, Object>();

params.put(“p_clob_bericht”, new SqlLobValue(xml, lh));

Map<String, Object> outparams = super.execute(params);

if (outparams.get(“p_err_msg”) != null) {

error = outparams.get(“p_err_msg”);

}

return error;

}

The bold parts in the code above do the trick, to get great chuncks of XML data in an Oracle Table

Tags: development, java, Oracle, spring

elstar IT

Java Tip: post Clob data to stored procedure in Oracle with Spring Framework

Leave a Reply Cancel reply