Hi All I am using spring simple JDBC template to call the oracle procedure the below are my code.
The procedure
create or replace
PROCEDURE get_all_system_users(
pi_client_code IN VARCHAR2,
po_system_users OUT T_SYSTEM_USER_TAB,
po_error_code OUT NUMBER,
po_error_description OUT VARCHAR2)
IS
ctr NUMBER;
sysUser SYSTEM_USER_OBJ;
BEGIN
ctr:=0;
po_system_users:= t_system_user_tab();
end
The Spring Dao class
public class ManualSaleStoredProcedureDao {
private SimpleJdbcCall getAllSytemUsers;
public List<SystemUser> getAllSytemUsers(String clientCode) {
MapSqlParameterSource in = new MapSqlParameterSource();
in.addValue("pi_client_code", clientCode);
in.addValue("po_system_users", null,
OracleTypes.ARRAY, "T_SYSTEM_USER_TAB");
Map<String, Object> result = getAllSytemUsers.execute(in);
return null;
}
public void setDataSource(DataSource dataSource) {
getAllSytemUsers = new SimpleJdbcCall(dataSource)
.withSchemaName("SChemaName")
.withProcedureName("get_all_system_users")
.declareParameters(
new SqlParameter(
"pi_client_code",
OracleTypes.VARCHAR,
"pi_client_code"));
}
When I am calling Map<String, Object> result = getAllSytemUsers.execute(in);
Iam getting the below exception
org.springframework.jdbc.UncategorizedSQLException: CallableStatementCallback; uncategorized SQLException for SQL [{call VSC.GET_ALL_SYSTEM_USERS(?, ?, ?, ?)}]; SQL state [99999]; error code [17004]; Invalid column type: 1111; nested exception is java.sql.SQLException: Invalid column type: 1111
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:83)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:1030)
at org.springframework.jdbc.core.JdbcTemplate.call(JdbcTemplate.java:1064)
at org.springframework.jdbc.core.simple.AbstractJdbcCall.executeCallInternal(AbstractJdbcCall.java:388)
at org.springframework.jdbc.core.simple.AbstractJdbcCall.doExecute(AbstractJdbcCall.java:351)
at org.springframework.jdbc.core.simple.SimpleJdbcCall.execute(SimpleJdbcCall.java:181)
at com.budco.vsc.dao.ManualSaleStoredProcedureDao.getAllSytemUsers(ManualSaleStoredProcedureDao.java:30)
at com.budco.vsc.dao.ManualSaleStoredProcedureDaoITest.getCustomerNotes(ManualSaleStoredProcedureDaoITest.java:64)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:601)
at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:44)
at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:15)
at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:41)
at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:20)
at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:28)
at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:74)
at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:83)
at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:72)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:231)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:49)
at org.junit.runners.ParentRunner$3.run(ParentRunner.java:193)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:52)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:191)
at org.junit.runners.ParentRunner.access$000(ParentRunner.java:42)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:184)
at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)
at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:71)
at org.junit.runners.ParentRunner.run(ParentRunner.java:236)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:174)
at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:50)
at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:467)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:683)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:390)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:197)
Caused by: java.sql.SQLException: Invalid column type: 1111
at oracle.jdbc.driver.OracleStatement.getInternalType(OracleStatement.java:3950)
at oracle.jdbc.driver.OracleCallableStatement.registerOutParameterInternal(OracleCallableStatement.java:135)
at oracle.jdbc.driver.OracleCallableStatement.registerOutParameter(OracleCallableStatement.java:304)
at oracle.jdbc.driver.OracleCallableStatement.registerOutParameter(OracleCallableStatement.java:393)
at oracle.jdbc.driver.OracleCallableStatementWrapper.registerOutParameter(OracleCallableStatementWrapper.java:1569)
at org.apache.commons.dbcp.DelegatingCallableStatement.registerOutParameter(DelegatingCallableStatement.java:95)
at org.apache.commons.dbcp.DelegatingCallableStatement.registerOutParameter(DelegatingCallableStatement.java:95)
at org.springframework.jdbc.core.CallableStatementCreatorFactory$CallableStatementCreatorImpl.createCallableStatement(CallableStatementCreatorFactory.java:198)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:1008)
... 35 more
This question is related to
java
oracle
spring
jdbctemplate
I have a function which returns a CLOB and I was seeing the above error when I'd forgotten to declare the return value as an output parameter. Initially I had:
protected SimpleJdbcCall buildJdbcCall(JdbcTemplate jdbcTemplate)
{
SimpleJdbcCall call = new SimpleJdbcCall(jdbcTemplate)
.withSchemaName(schema)
.withCatalogName(catalog)
.withFunctionName(functionName)
.withReturnValue()
.declareParameters(buildSqlParameters());
return call;
}
public SqlParameter[] buildSqlParameters() {
return new SqlParameter[]{
new SqlParameter("p_names", Types.VARCHAR),
new SqlParameter("p_format", Types.VARCHAR),
new SqlParameter("p_units", Types.VARCHAR),
new SqlParameter("p_datums", Types.VARCHAR),
new SqlParameter("p_start", Types.VARCHAR),
new SqlParameter("p_end", Types.VARCHAR),
new SqlParameter("p_timezone", Types.VARCHAR),
new SqlParameter("p_office_id", Types.VARCHAR),
};
}
The buildSqlParameters method should have included the SqlOutParameter:
public SqlParameter[] buildSqlParameters() {
return new SqlParameter[]{
new SqlParameter("p_names", Types.VARCHAR),
new SqlParameter("p_format", Types.VARCHAR),
new SqlParameter("p_units", Types.VARCHAR),
new SqlParameter("p_datums", Types.VARCHAR),
new SqlParameter("p_start", Types.VARCHAR),
new SqlParameter("p_end", Types.VARCHAR),
new SqlParameter("p_timezone", Types.VARCHAR),
new SqlParameter("p_office_id", Types.VARCHAR),
new SqlOutParameter("l_clob", Types.CLOB) // <-- This was missing!
};
}
I had this problem, and turns out the problem was that I had used
new SimpleJdbcCall(jdbcTemplate)
.withProcedureName("foo")
instead of
new SimpleJdbcCall(jdbcTemplate)
.withFunctionName("foo")
Probably, you need to insert schema identifier here:
in.addValue("po_system_users", null, OracleTypes.ARRAY, "your_schema.T_SYSTEM_USER_TAB");
I think the problem is with the datatype of the data you are passing Caused by: java.sql.SQLException: Invalid column type: 1111
check the datatypes you pass with the actual column datatypes may be there can be some mismatch or some constraint violation with null
We had the same issue when we had a typo in the mybatis mapping file like
....
#{column1Name, jdbcType=INTEGER},
#{column2Name, jdbcType=VARCHAR},
#{column3Name, jdbcTyep=VARCHAR} -- do you see the typo ?
.....
So check this kind of typos as well. Unfortunately, it can not understand the typo in compile/build time, it causes an unchecked exception and booms in runtime.
Source: Stackoverflow.com