SQL state 99999 error code 17004 Invalid column type 1111 With Spring SimpleJdbcCall

Question

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 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          When I am calling Map lt String  Object gt  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

User · Accepted Answer

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 lt SystemUser gt  getAllSytemUsers String clientCode         MapSqlParameterSource in   new MapSqlParameterSource        in addValue  pi client code   clientCode       Map lt String  Object gt  result   getAllSytemUsers execute in        SuppressWarnings  unchecked       List lt SystemUser gt  systemUsers    List lt SystemUser gt   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

User · Answer

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

User · Answer

Probably  you need to insert schema identifier here     in addValue  po system users   null  OracleTypes ARRAY   your schema T SYSTEM USER TAB

User · Answer

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       lt -- This was missing

User · Answer

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

User · Answer

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

[java] SQL state [99999]; error code [17004]; Invalid column type: 1111 With Spring SimpleJdbcCall

Examples related to java

Examples related to oracle

Examples related to spring

Examples related to jdbctemplate