SQL state [99999]; error code [17004]; Invalid column type; nested exception is java.sql.SQLException: Invalid column type

Question

I went through many link like : How to execute IN() SQL queries with Spring's JDBCTemplate effectivly?, but did not resolved my query. Not sure how to do this in jdbcTemplate

Could anyone please guide? Its a real blocker for me now :)

Code below

private static final String GET_PAYMENT_DT_FOR_BANK =
"select a.crte_dt, a.crte_user_id, a.acc_num from cust_mstr a where a.acc_num in (:acc_numList) and a.actv = :actv";

Here is the method which calls it ?

public List<Map<String, Object>> getAccDetailsFromAccNum(List<Long> acc_numList) {
//  Map<String, Object> params = new HashMap<String, Object>();
//  params.put("acc_numList", acc_numList);

    MapSqlParameterSource parameters = new MapSqlParameterSource();
    parameters.addValue("acc_numList", acc_numList);
    parameters.addValue("actv", "Y");

    return jdbcTemplate.queryForList(GET_PAYMENT_DT_FOR_BANK, parameters);
}

Error

java.lang.IllegalStateException: Failed to execute CommandLineRunner
    at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:800) [spring-boot-2.0.3.RELEASE.jar:2.0.3.RELEASE]
    at org.springframework.boot.SpringApplication.callRunners(SpringApplication.java:781) [spring-boot-2.0.3.RELEASE.jar:2.0.3.RELEASE]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:335) [spring-boot-2.0.3.RELEASE.jar:2.0.3.RELEASE]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1255) [spring-boot-2.0.3.RELEASE.jar:2.0.3.RELEASE]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1243) [spring-boot-2.0.3.RELEASE.jar:2.0.3.RELEASE]
    at com.example.DemoApplication.main(DemoApplication.java:26) [classes/:na]
Caused by: org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [select  a.crte_dt, a.crte_user_id, a.acc_num from from cust_mstr a where a.acc_num in (:acc_numList) and actv = :actv]; SQL state [99999]; error code [17004]; Invalid column type; nested exception is java.sql.SQLException: Invalid column type
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:89) ~[spring-jdbc-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) ~[spring-jdbc-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) ~[spring-jdbc-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1402) ~[spring-jdbc-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:620) ~[spring-jdbc-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:657) ~[spring-jdbc-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:688) ~[spring-jdbc-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:700) ~[spring-jdbc-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:751) ~[spring-jdbc-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.queryForList(JdbcTemplate.java:832) ~[spring-jdbc-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at com.example.MyBIllingService.getICADetailsFromIcaNum(MyBIllingService.java:44) ~[classes/:na]
    at com.example.MyBIllingService$$FastClassBySpringCGLIB$$61c86857.invoke(<generated>) ~[classes/:na]
    at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204) ~[spring-core-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:746) ~[spring-aop-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) ~[spring-aop-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:139) ~[spring-tx-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185) ~[spring-aop-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:688) ~[spring-aop-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at com.example.MyBIllingService$$EnhancerBySpringCGLIB$$d1b6a291.getICADetailsFromIcaNum(<generated>) ~[classes/:na]
    at com.example.DemoApplication.isIcaInTrailPeriod(DemoApplication.java:34) [classes/:na]
    at com.example.DemoApplication.run(DemoApplication.java:61) [classes/:na]
    at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:797) [spring-boot-2.0.3.RELEASE.jar:2.0.3.RELEASE]
    ... 5 common frames omitted
Caused by: java.sql.SQLException: Invalid column type
    at oracle.jdbc.driver.OraclePreparedStatement.setObjectCritical(OraclePreparedStatement.java:10495) ~[ojdbc7-12.1.0.2.jar:12.1.0.2.0]
    at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:9974) ~[ojdbc7-12.1.0.2.jar:12.1.0.2.0]
    at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:10799) ~[ojdbc7-12.1.0.2.jar:12.1.0.2.0]
    at oracle.jdbc.driver.OraclePreparedStatement.setObject(OraclePreparedStatement.java:10776) ~[ojdbc7-12.1.0.2.jar:12.1.0.2.0]
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.setObject(OraclePreparedStatementWrapper.java:241) ~[ojdbc7-12.1.0.2.jar:12.1.0.2.0]
    at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.setObject(HikariProxyPreparedStatement.java) ~[HikariCP-2.7.9.jar:na]
    at org.springframework.jdbc.core.StatementCreatorUtils.setValue(StatementCreatorUtils.java:411) ~[spring-jdbc-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValueInternal(StatementCreatorUtils.java:232) ~[spring-jdbc-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValue(StatementCreatorUtils.java:163) ~[spring-jdbc-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.jdbc.core.ArgumentPreparedStatementSetter.doSetValue(ArgumentPreparedStatementSetter.java:69) ~[spring-jdbc-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.jdbc.core.ArgumentPreparedStatementSetter.setValues(ArgumentPreparedStatementSetter.java:50) ~[spring-jdbc-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:664) ~[spring-jdbc-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:605) ~[spring-jdbc-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    ... 22 common frames omitted

The Data Type of acc_num is NUMBER(10,0). If I created JPA, then its

@Column(name="ACC_NUM")
private BigDecimal accNum;

Why its not working for BigDecimal, Integer and Long ? Oracle version 12c (12.1.0.2)

score 2 · Accepted Answer · answered Jul 25 '18 at 15:27

I was able to solve the below issue using NamedParameterJdbcTemplate. I wonder if this can be achieved using JdbcTemplate.

Below works fine.

@Override
public List<Map<String, Object>> getAccDetailsFromAccNum(List<Long> acc_numList) {
    Map<String, Set<Integer>> singletonMap = Collections.singletonMap("acc_numList", acc_numList);

    Map<String, Object> paramMap = new HashMap<>();
    paramMap.putAll(singletonMap);
    paramMap.put("isFlag", "Y");


    List<Map<String, Object>> queryForMap = namedParameterJdbcTemplate.queryForList(sql, paramMap);
    return queryForMap;
}

Code Reference: http://www.javarticles.com/2015/02/example-of-spring-namedparameterjdbctemplate.html

score 0 · Answer 2 · answered Jul 23 '18 at 09:59

0

acc_numList is type of List<Long> but the column as you said is Number. Change it to List<Integer>.

answered Jul 23 '18 at 09:59

mohammedkhan

953
6
14

@Prateek try running your query directly on the database with some values you would expect and see if it runs. If it works please paste the SQL you ran here. – mohammedkhan Jul 23 '18 at 13:01

SQL state [99999]; error code [17004]; Invalid column type; nested exception is java.sql.SQLException: Invalid column type

2 Answers2