Hello,
I'm using an Oracle stored procedure with an IN OUT CLOB parameter.
Code:
PROCEDURE ping(P_paramSet IN OUT CLOB) IS
BEGIN
P_paramSet := 'OK';
END ping3;
I've defined my procedure using Spring StoredProcedure class :
Code:
private class DemoStoredProcedure extends StoredProcedure {
public static final String SQL = "inv.ping";
public DemoStoredProcedure(DataSource ds) {
setDataSource(ds);
setSql(SQL);
setFunction(false);
declareParameter(new SqlOutParameter("request", Types.CLOB, null,
new SqlReturnType()
{
public Object getTypeValue(CallableStatement cs,
int paramIndex, int sqlType, String typeName)
throws SQLException
{
try
{
final Clob aClob = cs.getClob(paramIndex);
final Reader clobReader = aClob.getCharacterStream();
int length = (int) aClob.length();
char[] inputBuffer = new char[1024];
final StringBuffer outputBuffer = new StringBuffer();
while ((length = clobReader.read(inputBuffer)) != -1)
{
outputBuffer.append(inputBuffer, 0, length);
}
return outputBuffer.toString();
} catch (IOException e)
{
throw new SQLException(e.toString());
}
}
}));
compile();
}
}
When I call the procedure using DefaultLobHandler:
Code:
DefaultLobHandler lh = new DefaultLobHandler();
HashMap map = new HashMap();
map.put("request", new SqlLobValue("myValue", lh));
storedProcedure.execute(map);
I have the following Exception and no result :
Code:
org.springframework.jdbc.UncategorizedSQLException: CallableStatementCallback; uncategorized SQLException for SQL [{call InvestorProfile.ping3(?)}]; SQL state [null]; error code [17012]; Parameter Type Conflict; nested exception is java.sql.SQLException: Parameter Type Conflict
java.sql.SQLException: Parameter Type Conflict
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:208)
at oracle.jdbc.driver.OraclePreparedStatement.processCompletedBindRow(OraclePreparedStatement.java:2066)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3280)
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3390)
at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4223)
at org.springframework.jdbc.core.JdbcTemplate$5.doInCallableStatement(JdbcTemplate.java:859)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:824)
at org.springframework.jdbc.core.JdbcTemplate.call(JdbcTemplate.java:857)
at org.springframework.jdbc.object.StoredProcedure.execute(StoredProcedure.java:102)
at TestStandAloneBlob$DemoStoredProcedure.execute(TestStandAloneBlob.java:97)
at TestStandAloneBlob.execute(TestStandAloneBlob.java:51)
at TestStandAloneBlob.main(TestStandAloneBlob.java:37)
When I call the procedure using OracleLobHandler:
Code:
OracleLobHandler lh = new OracleLobHandler();
HashMap map = new HashMap();
map.put("request", new SqlLobValue("myValue", lh));
storedProcedure.execute(map);
I have the following Exception, but the result is OK (in OracleLobHandler, exception is caught, logged but not thrown) :
Code:
2006-01-10 11:50:43,114 [main] [ERROR] org.springframework.jdbc.support.lob.OracleLobHandler.close(409) | Could not free Oracle LOB
java.sql.SQLException: ORA-22922: nonexistent LOB value
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:283)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:278)
at oracle.jdbc.driver.T4C8TTILob.receiveReply(T4C8TTILob.java:930)
at oracle.jdbc.driver.T4C8TTILob.freeTemporaryLob(T4C8TTILob.java:411)
at oracle.jdbc.driver.T4CConnection.freeTemporary(T4CConnection.java:2854)
at oracle.sql.CLOB.freeTemporary(CLOB.java:815)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at org.springframework.jdbc.support.lob.OracleLobHandler$OracleLobCreator.close(OracleLobHandler.java:404)
at org.springframework.jdbc.core.support.SqlLobValue.cleanup(SqlLobValue.java:223)
at org.springframework.jdbc.core.StatementCreatorUtils.cleanupParameters(StatementCreatorUtils.java:221)
at org.springframework.jdbc.core.CallableStatementCreatorFactory$CallableStatementCreatorImpl.cleanupParameters(CallableStatementCreatorFactory.java:227)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:845)
at org.springframework.jdbc.core.JdbcTemplate.call(JdbcTemplate.java:857)
at org.springframework.jdbc.object.StoredProcedure.execute(StoredProcedure.java:102)
at TestStandAloneBlob$DemoStoredProcedure.execute(TestStandAloneBlob.java:97)
at TestStandAloneBlob.execute(TestStandAloneBlob.java:51)
at TestStandAloneBlob.main(TestStandAloneBlob.java:37)
I'm afraid that, when running in an application server (it's only test now), this SQL exception cause a RollBack...
So, what is the good LobHandler to use. Did I do something wrong ?
Thank for your Help.
For information :- I'm using org.springframework.jdbc.datasource.DriverManagerD ataSource as DataSource
- using the following JDBC give the result without exception :
Code:
String SQL = "Call InvestorProfile.ping(?)";
stmt = connection.prepareCall(SQL);
stmt.setString(1, request);
stmt.registerOutParameter(1, Types.VARCHAR);
stmt.execute();
logger.info(stmt.getString(1));
- I'm using Oracle9i Enterprise Edition Release 9.2.0.6.0
- I'm using latest oracle Driver : 10.2.0.1.0
- I had tried with both THIN and OCI drivers
Stored procedure with IN OUT CLOB
Reply With Quote
