SQL state [99999]; error code [17004]; Invalid column type: 1111 With Spring SimpleJdbcCall
Solution 1
Finally I solve the issues using below code. This type of error will happen when there is a mismatch between In/Out parameter as declare in procedure and in java code declareParameters
. Here we need to defined oracle return tab
public class ManualSaleStoredProcedureDao {
private SimpleJdbcCall getAllSytemUsers;
public List<SystemUser> getAllSytemUsers(String clientCode) {
MapSqlParameterSource in = new MapSqlParameterSource();
in.addValue("pi_client_code", clientCode);
Map<String, Object> result = getAllSytemUsers.execute(in);
@SuppressWarnings("unchecked")
List<SystemUser> systemUsers = (List<SystemUser>) result
.get(VSCConstants.GET_SYSTEM_USER_OUT_PARAM1);
return systemUsers;
}
public void setDataSource(DataSource dataSource) {
getAllSytemUsers = new SimpleJdbcCall(dataSource)
.withSchemaName(VSCConstants.SCHEMA)
.withProcedureName(VSCConstants.GET_SYSTEM_USER_PROC_NAME)
.declareParameters(
new SqlParameter(
"pi_client_code",
OracleTypes.NUMBER,
"pi_client_code"),
new SqlInOutParameter(
"po_system_users",
OracleTypes.ARRAY,
"T_SYSTEM_USER_TAB",
new OracleSystemUser()));
}
Solution 2
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
Solution 3
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!
};
}
Solution 4
Probably, you need to insert schema identifier here:
in.addValue("po_system_users", null, OracleTypes.ARRAY, "your_schema.T_SYSTEM_USER_TAB");
Comments
-
Krushna almost 2 years
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 exceptionorg.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
-
Egor Skriptunoff about 11 years@KrushnaCh.Dash - Are parameters for
new SqlParameter()
correct? -
Krushna about 11 yearsYes adding SQL parameter is optional as i have mention the type at the top
-
Murat Derya Özen almost 10 yearsWhy do you not include how you solved it instead of pasting code?
-
Krushna over 8 yearsPlease see the edits, The error will happen due to mismatch of IN/OUT parameter as declare in procedure and java code
-
Smart Coder about 2 yearssometimes it could be enum type value that interferes with data type.