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:
