title: SQL state [99999]; error code [17004]; Invalid column type: 1111 With Spring SimpleJdbcCall
body: <p>Hi All I am using spring simple JDBC template to call the oracle procedure the below are my code.</p>
  
  <p>The procedure</p>
  
  <pre><code>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
  </code></pre>
  
  <p>The Spring Dao class</p>
  
  <pre><code>public class ManualSaleStoredProcedureDao {
  
  private SimpleJdbcCall getAllSytemUsers;
  
  public List&lt;SystemUser&gt; 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&lt;String, Object&gt; 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"));
  
  }
  </code></pre>
  
  <p>When I am calling <code>Map&lt;String, Object&gt; result = getAllSytemUsers.execute(in);</code>
  Iam getting the below exception</p>
  
  <pre><code>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
  </code></pre>