CLOB Support for Oracle stored procedure

[Christian Dupuis]

Hallo everybody,

I'm currently struggling with writing an clob field in oracle 8 database using build in stored procedure support of spring jdbc package.

I have the following class:

private static class InsertTransaction extends StoredProcedure {

        public InsertTransaction(DataSource ds) {
            super(ds, ECUTransactionDAO.INSERT_PROCEDURE);
            declareParameter(new SqlParameter("REQUEST", Types.CLOB));
            compile();
        }

        public Map execute(String request) {
            HashMap map = new HashMap();
            map.put("REQUEST", request);
 
            return super.execute(map);
        }
    }

Calling the execute method gives me a

java.lang.ClassCastException
	at oracle.jdbc.driver.OraclePreparedStatement.setObject(OraclePreparedStatement.java:3069)
	at oracle.jdbc.driver.OraclePreparedStatement.setObject(OraclePreparedStatement.java:3107)
	at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValue(StatementCreatorUtils.java:107)
	at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValue(StatementCreatorUtils.java:62)
	at org.springframework.jdbc.core.CallableStatementCreatorFactory$CallableStatementCreatorImpl.createCallableStatement(CallableStatementCreatorFactory.java:193)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:581)
	at org.springframework.jdbc.core.JdbcTemplate.call(JdbcTemplate.java:610)
	at org.springframework.jdbc.object.StoredProcedure.execute(StoredProcedure.java:101)

which I is quite obvious because I try to write a String in an CLOB field.
But what is the correct way getting a huge string in an clob field using an
stored procedure?

Any hints or pointers to examples?

Thanks

Christian Dupuis

[trisberg]

I have not used any of this against an Oracle 8 database but it does work with 8i/9i/10g. If you use the latest 10g JDBC driver then the following code should be sufficient:

	map.put("REQUEST", new SqlLobValue(request));

If you are using the 9i JDBC driver then you need to use an OracleLobHandler like this (this also works with the 10g driver):

	OracleLobHandler lh = new OracleLobHandler();
	map.put("REQUEST", new SqlLobValue(request, lh));

If you are running with a connection pool that wraps the physical Oracle connection then you might need a NativeJdbcExtractor implementation. here is an example that works for Apache DBCP

	OracleLobHandler lh = new OracleLobHandler();
	lh.setNativeJdbcExtractor(new SimpleNativeJdbcExtractor());
	map.put("REQUEST", new SqlLobValue(request, lh));

There are some other implementations provided as well:

• JBossNativeJdbcExtractor.java
  WebLogicNativeJdbcExtractor.java
  WebSphereNativeJdbcExtractor.java
  XAPoolNativeJdbcExtractor.java

[Christian Dupuis]

Thanks for your help.

After some additional struggling, I figured out how to use your examples within Weblogic 8.1 SP2 (Oracle 9.1i JDBC Driver).

Declaring an CLOB input parameter on my stored procedure and the following code works just fine within the Weblogic Container.

package clob;

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

import javax.sql.DataSource;

import org.springframework.context.ApplicationContextException;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.jdbc.core.support.SqlLobValue;
import org.springframework.jdbc.object.StoredProcedure;
import org.springframework.jdbc.support.lob.OracleLobHandler;
import org.springframework.jdbc.support.nativejdbc.WebLogicNativeJdbcExtractor;

public class ClobDao
        extends JdbcDaoSupport {

    private InsertTransaction insert;

    private static final String INSERT_PROCEDURE = "TEST_CLOB";
    
    protected void initDao() throws ApplicationContextException {
        insert = new InsertTransaction(getDataSource());
    }

    public void set(String status) {
        this.insert.execute(status);
    }

    private static class InsertTransaction
            extends StoredProcedure {

        public InsertTransaction(DataSource ds) {
            super(ds, ClobDao.INSERT_PROCEDURE);
            declareParameter(new SqlParameter("CLOBTEST_", Types.CLOB));
            compile();
        }

        public Map execute(String status) {
            try {
                HashMap map = new HashMap();
                OracleLobHandler lh = new OracleLobHandler();
                lh.setNativeJdbcExtractor(new WebLogicNativeJdbcExtractor());

                map.put("CLOBTEST_", new SqlLobValue(status, lh));

                return super.execute(map);
            }
            catch (Exception e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            return new HashMap();
        }
    }
}

Thanks again for your great support.

Greetings Christian

P.S. The above code is only for testing purpose. It is not intended for production use. Thats why there are catch(Exception e) and so on.

[Flynn]

Just thought that I'd mention that I found this info really helpful. I've had loads of issues with using CLOBs in the past due to connection pooling and it took me no more than 20 mins to get them working using CLOBs.

[Franklin Antony]

This post has been very helpful. However I am facing a major issue with the OracleLobHandler. When I use it along with the Spring Stored Procedure, I am able to insert into the table BLOBs. However when I put it in a loop and try to insert into tables, something like 10 BLOBs, one after another, it burst at the 4th BLOB saying that cannot free BLOB. But it works fine for like 1,2 or even 3 blobs, but burst on the 4th and more. However this works absolutely fine when using the jdbcTemplate directly to insert into the table. Has anyone faced the same issue.

Thanks,
Franklin

[radegast]

I found useful the chapter 6 of the book Building Spring 2 Enterprise Applications (Apress) covering the database access using JDBC templates.

[Franklin Antony]

Thanks radegast .
If you could send me a sample content from this chapter I could check it out. Else if you could try out the same in a loop with the LOBs it would be really helpful.



Thanks,
Franklin.