Total Pageviews

2014/08/27

ORA-17004 : Invalid Column Type

Problem
I am using JPA(Java Persistence API) as my persistence tier technology to write data into Oracle database. Here is the database schema for the targeted table:
Column
Type
Size
Nulls
Default
Comments
EXE_ID
number
20
執行ID, Calendar.getInstance().getTimeInMillis()
FUNCTION_ID
varchar2
20
功能代號
MESSAGE
nvarchar2
2000
 V 
null
執行訊息
EXECUTE_TYPE
varchar2
1
V
null
C:CRON JOB, M: MANNUAL, U:Upload
USER_ID
varchar2
20
 V 
null
批次執行時,帶功能名稱,手動執行時,帶執行人USER_ID
UPDATE_DATE_TIME
timestamp(6)
11,6
 V 
now
執行時間
FILE_NAME
varchar2
40
 V 
null
檔案名稱

Here is the code snippet:
1:    public void writeLog(Boolean isSuccessful, String funId, String exceptionMsg) {  
2:      try {  
3:        StringBuilder sql = new StringBuilder();  
4:        sql.append("INSERT INTO FMS900FA(EXE_ID, FUNCTION_ID, MESSAGE, EXECUTE_TYPE, USER_ID, FILE_NAME)");  
5:        sql.append(" VALUES(:EXE_ID, :FUNCTION_ID, :MESSAGE, :EXECUTE_TYPE, :USER_ID, :FILE_NAME)");  
6:        Map<String, Object> params = new HashMap<String, Object>();  
7:        params.put("EXE_ID", BigInteger.valueOf(Calendar.getInstance().getTimeInMillis()));  
8:        params.put("FUNCTION_ID", funId);  
9:        if (isSuccessful) {  
10:          params.put("MESSAGE", "執行成功");  
11:        } else {  
12:          params.put("MESSAGE", exceptionMsg);  
13:        }  
14:        params.put("EXECUTE_TYPE", "C");  
15:        params.put("USER_ID", "ADMIN");  
16:        params.put("FILE_NAME", "NA");  
17:        sqlExecutor.insert(sql, params);  
18:      } catch(Exception e) {  
19:        throw e;  
20:      }  
21:    }   

But program throw SQLException
1:  Caused by: org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [INSERT INTO FMS900FA(EXE_ID, FUNCTION_ID, MESSAGE, EXECUTE_TYPE, USER_ID, FILE_NAME)  VALUES(?, ?, ?, ?, ?, ?) ]; SQL state [99999]; error code [17004]; invalid column type; nested exception is java.sql.SQLException: invalid column type  
2:       at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:84) ~[spring-jdbc-4.0.3.RELEASE.jar!/:4.0.3.RELEASE]  
3:       at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) ~[spring-jdbc-4.0.3.RELEASE.jar!/:4.0.3.RELEASE]  
4:       at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) ~[spring-jdbc-4.0.3.RELEASE.jar!/:4.0.3.RELEASE]  
5:       at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:660) ~[spring-jdbc-4.0.3.RELEASE.jar!/:4.0.3.RELEASE]  
6:       at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:909) ~[spring-jdbc-4.0.3.RELEASE.jar!/:4.0.3.RELEASE]  
7:       at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:933) ~[spring-jdbc-4.0.3.RELEASE.jar!/:4.0.3.RELEASE]  
8:       at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.update(NamedParameterJdbcTemplate.java:313) ~[spring-jdbc-4.0.3.RELEASE.jar!/:4.0.3.RELEASE]  
9:       at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.update(NamedParameterJdbcTemplate.java:318) ~[spring-jdbc-4.0.3.RELEASE.jar!/:4.0.3.RELEASE]  
10:       at com.cht.commons.persistence.query.SqlExecutor.execute(SqlExecutor.java:97) ~[cht-commons-persistence-0.1.0-SNAPSHOT.jar!/:0.1.0-SNAPSHOT]  
11:       at com.cht.commons.persistence.query.SqlExecutor.insert(SqlExecutor.java:157) ~[cht-commons-persistence-0.1.0-SNAPSHOT.jar!/:0.1.0-SNAPSHOT]  
12:       at com.cht.commons.persistence.query.SqlExecutor.insert(SqlExecutor.java:146) ~[cht-commons-persistence-0.1.0-SNAPSHOT.jar!/:0.1.0-SNAPSHOT]  
13:       at gov.nta.fms.service.FmsBatchLog.writeLog(FmsBatchLog.java:139) ~[fms-service-1.0.0-SNAPSHOT.jar:1.0.0-SNAPSHOT]  
14:       at gov.nta.fms.service.Fms435xService.initFms435fa(Fms435xService.java:228) ~[fms-service-1.0.0-SNAPSHOT.jar:1.0.0-SNAPSHOT]  
15:       at gov.nta.fms.service.Fms435xService$$FastClassBySpringCGLIB$$75f6a95.invoke() ~[na:na]  
16:       at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204) ~[spring-core-4.0.3.RELEASE.jar!/:4.0.3.RELEASE]  
17:       at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:711) ~[spring-aop-4.0.3.RELEASE.jar!/:4.0.3.RELEASE]  
18:       at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157) ~[spring-aop-4.0.3.RELEASE.jar!/:4.0.3.RELEASE]  
19:       at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:98) ~[spring-tx-4.0.3.RELEASE.jar!/:4.0.3.RELEASE]  
20:       at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:262) ~[spring-tx-4.0.3.RELEASE.jar!/:4.0.3.RELEASE]  
21:       at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:95) ~[spring-tx-4.0.3.RELEASE.jar!/:4.0.3.RELEASE]  
22:       at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) ~[spring-aop-4.0.3.RELEASE.jar!/:4.0.3.RELEASE]  
23:       at org.springframework.aop.aspectj.AspectJAfterThrowingAdvice.invoke(AspectJAfterThrowingAdvice.java:58) ~[spring-aop-4.0.3.RELEASE.jar!/:4.0.3.RELEASE]  
24:       ... 111 common frames omitted  
25:  Caused by: java.sql.SQLException: invalid column type  
26:       at oracle.jdbc.driver.OraclePreparedStatement.setObjectCritical(OraclePreparedStatement.java:8761) ~[na:na]  
27:       at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:8259) ~[na:na]  
28:       at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:9012) ~[na:na]  
29:       at oracle.jdbc.driver.OraclePreparedStatement.setObject(OraclePreparedStatement.java:8993) ~[na:na]  
30:       at oracle.jdbc.driver.OraclePreparedStatementWrapper.setObject(OraclePreparedStatementWrapper.java:230) ~[na:na]  
31:       at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.setObject(WrappedPreparedStatement.java:986) ~[na:na]  
32:       at org.springframework.jdbc.core.StatementCreatorUtils.setValue(StatementCreatorUtils.java:402) ~[spring-jdbc-4.0.3.RELEASE.jar!/:4.0.3.RELEASE]  
33:       at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValueInternal(StatementCreatorUtils.java:235) ~[spring-jdbc-4.0.3.RELEASE.jar!/:4.0.3.RELEASE]  
34:       at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValue(StatementCreatorUtils.java:150) ~[spring-jdbc-4.0.3.RELEASE.jar!/:4.0.3.RELEASE]  
35:       at org.springframework.jdbc.core.PreparedStatementCreatorFactory$PreparedStatementCreatorImpl.setValues(PreparedStatementCreatorFactory.java:300) ~[spring-jdbc-4.0.3.RELEASE.jar!/:4.0.3.RELEASE]  
36:       at org.springframework.jdbc.core.PreparedStatementCreatorFactory$PreparedStatementCreatorImpl.createPreparedStatement(PreparedStatementCreatorFactory.java:252) ~[spring-jdbc-4.0.3.RELEASE.jar!/:4.0.3.RELEASE]  
37:       at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:638) ~[spring-jdbc-4.0.3.RELEASE.jar!/:4.0.3.RELEASE]  
38:       ... 129 common frames omitted  

How to solve it?
This SQLException, ORA-17004 : Invalid Column Type, results from EXE_ID this column.

We use BigInteger originally, but fail to do insert. As we change to use BigDecimal, it's working fine now. But the root cause is still unknown.
1:    public void writeLog(Boolean isSuccessful, String funId, String exceptionMsg) {  
2:      try {  
3:        StringBuilder sql = new StringBuilder();  
4:        sql.append("INSERT INTO FMS900FA(EXE_ID, FUNCTION_ID, MESSAGE, EXECUTE_TYPE, USER_ID, FILE_NAME)");  
5:        sql.append(" VALUES(:EXE_ID, :FUNCTION_ID, :MESSAGE, :EXECUTE_TYPE, :USER_ID, :FILE_NAME)");  
6:        Map<String, Object> params = new HashMap<String, Object>();  
7:        params.put("EXE_ID", new BigDecimal(Calendar.getInstance().getTimeInMillis()));  
8:        params.put("FUNCTION_ID", funId);  
9:        if (isSuccessful) {  
10:          params.put("MESSAGE", "執行成功");  
11:        } else {  
12:          params.put("MESSAGE", exceptionMsg);  
13:        }  
14:        params.put("EXECUTE_TYPE", "C");  
15:        params.put("USER_ID", "ADMIN");  
16:        params.put("FILE_NAME", "NA");  
17:        sqlExecutor.insert(sql, params);  
18:      } catch(Exception e) {  
19:        throw e;  
20:      }  
21:  

No comments: