同步到oracle时报错：SQL state [null]; error code [17004];
pid:1 nid:1 exception:setl:com.alibaba.otter.node.etl.load.exception.LoadException: java.util.concurrent.ExecutionException: com.alibaba.otter.node.etl.load.exception.LoadException: com.alibaba.otter.node.etl.load.exception.LoadException: com.alibaba.otter.node.etl.load.exception.LoadException: org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [merge /+ use_nl(a b)/ into ucr_order_mgr.tf_m_staff_group a using (select ? as UPDATE_TIME , ? as UPDATE_STAFF_ID , ? as GROUP_ID , ? as STAFF_ID , ? as STAFF_ROLE from dual) b on (a.GROUP_ID=b.GROUP_ID and a.STAFF_ID=b.STAFF_ID and a.STAFF_ROLE=b.STAFF_ROLE) when matched then update set a.UPDATE_TIME=b.UPDATE_TIME , a.UPDATE_STAFF_ID=b.UPDATE_STAFF_ID when not matched then insert (a.UPDATE_TIME , a.UPDATE_STAFF_ID , a.GROUP_ID , a.STAFF_ID , a.STAFF_ROLE ) values (b.UPDATE_TIME , b.UPDATE_STAFF_ID , b.GROUP_ID , b.STAFF_ID , b.STAFF_ROLE )]; SQL state [null]; error code [17004]; Invalid column type; nested exception is java.sql.SQLException: Invalid column type 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:603) at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:812) at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:868) at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction$DbLoadWorker$2.doInTransaction(DbLoadAction.java:621) at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:130) at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction$DbLoadWorker.doCall(DbLoadAction.java:613) at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction$DbLoadWorker.call(DbLoadAction.java:541) at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction.doTwoPhase(DbLoadAction.java:458) at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction.doLoad(DbLoadAction.java:275) at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction.load(DbLoadAction.java:161) at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction$$FastClassByCGLIB$$d932a4cb.invoke() at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:191) at org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:618) at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction$$EnhancerByCGLIB$$80fd23c2.load() at com.alibaba.otter.node.etl.load.loader.db.DataBatchLoader$2.call(DataBatchLoader.java:198) at com.alibaba.otter.node.etl.load.loader.db.DataBatchLoader$2.call(DataBatchLoader.java:189) at java.util.concurrent.FutureTask.run(FutureTask.java:262) at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471) at java.util.concurrent.FutureTask.run(FutureTask.java:262) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) at java.lang.Thread.run(Thread.java:745) Caused by: java.sql.SQLException: Invalid column type at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112) at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146) at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:208) at oracle.jdbc.driver.OraclePreparedStatement.setObjectCritical(OraclePreparedStatement.java:9231) at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:8812) at oracle.jdbc.driver.OraclePreparedStatement.setObject(OraclePreparedStatement.java:9285) at org.apache.commons.dbcp.DelegatingPreparedStatement.setObject(DelegatingPreparedStatement.java:166) at org.apache.commons.dbcp.DelegatingPreparedStatement.setObject(DelegatingPreparedStatement.java:166) at org.springframework.jdbc.core.StatementCreatorUtils.setValue(StatementCreatorUtils.java:370) at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValueInternal(StatementCreatorUtils.java:217) at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValue(StatementCreatorUtils.java:164) at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction$DbLoadWorker.doPreparedStatement(DbLoadAction.java:801) at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction$DbLoadWorker.access$800(DbLoadAction.java:513) at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction$DbLoadWorker$2$1.setValues(DbLoadAction.java:624) at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:816) at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:1) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:587) ... 21 more • PairId: 2 , TableId: 10 , EventType : I , Time : 1488720096000 • Consistency : , Mode :
---Pks EventColumn[index=0,columnType=3,columnName=GROUP_ID,columnValue=1,isNull=false,isKey=true,isUpdate=true] EventColumn[index=1,columnType=1111,columnName=STAFF_ID,columnValue=1,isNull=false,isKey=true,isUpdate=true] EventColumn[index=2,columnType=1111,columnName=STAFF_ROLE,columnValue=1,isNull=false,isKey=true,isUpdate=true] ---oldPks ---Columns EventColumn[index=3,columnType=91,columnName=UPDATE_TIME,columnValue=2017-03-05 00:00:00,isNull=false,isKey=false,isUpdate=true] EventColumn[index=4,columnType=1111,columnName=UPDATE_STAFF_ID,columnValue=1,isNull=false,isKey=false,isUpdate=true] ---Sql merge /+ use_nl(a b)/ into ucr_order_mgr.tf_m_staff_group a using (select ? as UPDATE_TIME , ? as UPDATE_STAFF_ID , ? as GROUP_ID , ? as STAFF_ID , ? as STAFF_ROLE from dual) b on (a.GROUP_ID=b.GROUP_ID and a.STAFF_ID=b.STAFF_ID and a.STAFF_ROLE=b.STAFF_ROLE) when matched then update set a.UPDATE_TIME=b.UPDATE_TIME , a.UPDATE_STAFF_ID=b.UPDATE_STAFF_ID when not matched then insert (a.UPDATE_TIME , a.UPDATE_STAFF_ID , a.GROUP_ID , a.STAFF_ID , a.STAFF_ROLE ) values (b.UPDATE_TIME , b.UPDATE_STAFF_ID , b.GROUP_ID , b.STAFF_ID , b.STAFF_ROLE ) Caused by: java.util.concurrent.ExecutionException: com.alibaba.otter.node.etl.load.exception.LoadException: com.alibaba.otter.node.etl.load.exception.LoadException: com.alibaba.otter.node.etl.load.exception.LoadException: org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [merge /+ use_nl(a b)/ into ucr_order_mgr.tf_m_staff_group a using (select ? as UPDATE_TIME , ? as UPDATE_STAFF_ID , ? as GROUP_ID , ? as STAFF_ID , ? as STAFF_ROLE from dual) b on (a.GROUP_ID=b.GROUP_ID and a.STAFF_ID=b.STAFF_ID and a.STAFF_ROLE=b.STAFF_ROLE) when matched then update set a.UPDATE_TIME=b.UPDATE_TIME , a.UPDATE_STAFF_ID=b.UPDATE_STAFF_ID when not matched then insert (a.UPDATE_TIME , a.UPDATE_STAFF_ID , a.GROUP_ID , a.STAFF_ID , a.STAFF_ROLE ) values (b.UPDATE_TIME , b.UPDATE_STAFF_ID , b.GROUP_ID , b.STAFF_ID , b.STAFF_ROLE )]; SQL state [null]; error code [17004]; Invalid column type; nested exception is java.sql.SQLException: Invalid column type 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:603) at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:812) at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:868) at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction$DbLoadWorker$2.doInTransaction(DbLoadAction.java:621) at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:130) at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction$DbLoadWorker.doCall(DbLoadAction.java:613) at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction$DbLoadWorker.call(DbLoadAction.java:541) at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction.doTwoPhase(DbLoadAction.java:458) at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction.doLoad(DbLoadAction.java:275) at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction.load(DbLoadAction.java:161) at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction$$FastClassByCGLIB$$d932a4cb.invoke() at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:191) at org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:618) at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction$$EnhancerByCGLIB$$80fd23c2.load() at com.alibaba.otter.node.etl.load.loader.db.DataBatchLoader$2.call(DataBatchLoader.java:198) at com.alibaba.otter.node.etl.load.loader.db.DataBatchLoader$2.call(DataBatchLoader.java:189) at java.util.concurrent.FutureTask.run(FutureTask.java:262) at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471) at java.util.concurrent.FutureTask.run(FutureTask.java:262) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) at java.lang.Thread.run(Thread.java:745) Caused by: java.sql.SQLException: Invalid column type at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112) at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146) at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:208) at oracle.jdbc.driver.OraclePreparedStatement.setObjectCritical(OraclePreparedStatement.java:9231) at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:8812) at oracle.jdbc.driver.OraclePreparedStatement.setObject(OraclePreparedStatement.java:9285) at org.apache.commons.dbcp.DelegatingPreparedStatement.setObject(DelegatingPreparedStatement.java:166) at org.apache.commons.dbcp.DelegatingPreparedStatement.setObject(DelegatingPreparedStatement.java:166) at org.springframework.jdbc.core.StatementCreatorUtils.setValue(StatementCreatorUtils.java:370) at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValueInternal(StatementCreatorUtils.java:217) at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValue(StatementCreatorUtils.java:164) at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction$DbLoadWorker.doPreparedStatement(DbLoadAction.java:801) at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction$DbLoadWorker.access$800(DbLoadAction.java:513) at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction$DbLoadWorker$2$1.setValues(DbLoadAction.java:624) at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:816) at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:1) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:587) ... 21 more • PairId: 2 , TableId: 10 , EventType : I , Time : 1488720096000 • Consistency : , Mode :
---Pks EventColumn[index=0,columnType=3,columnName=GROUP_ID,columnValue=1,isNull=false,isKey=true,isUpdate=true] EventColumn[index=1,columnType=1111,columnName=STAFF_ID,columnValue=1,isNull=false,isKey=true,isUpdate=true] EventColumn[index=2,columnType=1111,columnName=STAFF_ROLE,columnValue=1,isNull=false,isKey=true,isUpdate=true] ---oldPks ---Columns EventColumn[index=3,columnType=91,columnName=UPDATE_TIME,columnValue=2017-03-05 00:00:00,isNull=false,isKey=false,isUpdate=true] EventColumn[index=4,columnType=1111,columnName=UPDATE_STAFF_ID,columnValue=1,isNull=false,isKey=false,isUpdate=true] ---Sql merge /+ use_nl(a b)/ into ucr_order_mgr.tf_m_staff_group a using (select ? as UPDATE_TIME , ? as UPDATE_STAFF_ID , ? as GROUP_ID , ? as STAFF_ID , ? as STAFF_ROLE from dual) b on (a.GROUP_ID=b.GROUP_ID and a.STAFF_ID=b.STAFF_ID and a.STAFF_ROLE=b.STAFF_ROLE) when matched then update set a.UPDATE_TIME=b.UPDATE_TIME , a.UPDATE_STAFF_ID=b.UPDATE_STAFF_ID when not matched then insert (a.UPDATE_TIME , a.UPDATE_STAFF_ID , a.GROUP_ID , a.STAFF_ID , a.STAFF_ROLE ) values (b.UPDATE_TIME , b.UPDATE_STAFF_ID , b.GROUP_ID , b.STAFF_ID , b.STAFF_ROLE ) at java.util.concurrent.FutureTask.report(FutureTask.java:122) at java.util.concurrent.FutureTask.get(FutureTask.java:188) at com.alibaba.otter.node.etl.load.loader.db.DataBatchLoader.load(DataBatchLoader.java:107) at com.alibaba.otter.node.etl.load.loader.OtterLoaderFactory.load(OtterLoaderFactory.java:50) at com.alibaba.otter.node.etl.load.LoadTask$1.run(LoadTask.java:85) at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471) at java.util.concurrent.FutureTask.run(FutureTask.java:262) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) at java.lang.Thread.run(Thread.java:745) Caused by: com.alibaba.otter.node.etl.load.exception.LoadException: com.alibaba.otter.node.etl.load.exception.LoadException: com.alibaba.otter.node.etl.load.exception.LoadException: org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [merge /+ use_nl(a b)/ into ucr_order_mgr.tf_m_staff_group a using (select ? as UPDATE_TIME , ? as UPDATE_STAFF_ID , ? as GROUP_ID , ? as STAFF_ID , ? as STAFF_ROLE from dual) b on (a.GROUP_ID=b.GROUP_ID and a.STAFF_ID=b.STAFF_ID and a.STAFF_ROLE=b.STAFF_ROLE) when matched then update set a.UPDATE_TIME=b.UPDATE_TIME , a.UPDATE_STAFF_ID=b.UPDATE_STAFF_ID when not matched then insert (a.UPDATE_TIME , a.UPDATE_STAFF_ID , a.GROUP_ID , a.STAFF_ID , a.STAFF_ROLE ) values (b.UPDATE_TIME , b.UPDATE_STAFF_ID , b.GROUP_ID , b.STAFF_ID , b.STAFF_ROLE )]; SQL state [null]; error code [17004]; Invalid column type; nested exception is java.sql.SQLException: Invalid column type 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:603) at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:812) at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:868) at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction$DbLoadWorker$2.doInTransaction(DbLoadAction.java:621) at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:130) at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction$DbLoadWorker.doCall(DbLoadAction.java:613) at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction$DbLoadWorker.call(DbLoadAction.java:541) at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction.doTwoPhase(DbLoadAction.java:458) at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction.doLoad(DbLoadAction.java:275) at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction.load(DbLoadAction.java:161) at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction$$FastClassByCGLIB$$d932a4cb.invoke() at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:191) at org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:618) at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction$$EnhancerByCGLIB$$80fd23c2.load() at com.alibaba.otter.node.etl.load.loader.db.DataBatchLoader$2.call(DataBatchLoader.java:198) at com.alibaba.otter.node.etl.load.loader.db.DataBatchLoader$2.call(DataBatchLoader.java:189) at java.util.concurrent.FutureTask.run(FutureTask.java:262) at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471) at java.util.concurrent.FutureTask.run(FutureTask.java:262) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) at java.lang.Thread.run(Thread.java:745) Caused by: java.sql.SQLException: Invalid column type at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112) at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146) at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:208) at oracle.jdbc.driver.OraclePreparedStatement.setObjectCritical(OraclePreparedStatement.java:9231) at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:8812) at oracle.jdbc.driver.OraclePreparedStatement.setObject(OraclePreparedStatement.java:9285) at org.apache.commons.dbcp.DelegatingPreparedStatement.setObject(DelegatingPreparedStatement.java:166) at org.apache.commons.dbcp.DelegatingPreparedStatement.setObject(DelegatingPreparedStatement.java:166) at org.springframework.jdbc.core.StatementCreatorUtils.setValue(StatementCreatorUtils.java:370) at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValueInternal(StatementCreatorUtils.java:217) at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValue(StatementCreatorUtils.java:164) at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction$DbLoadWorker.doPreparedStatement(DbLoadAction.java:801) at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction$DbLoadWorker.access$800(DbLoadAction.java:513) at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction$DbLoadWorker$2$1.setValues(DbLoadAction.java:624) at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:816) at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:1) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:587) ... 21 more • PairId: 2 , TableId: 10 , EventType : I , Time : 1488720096000 • Consistency : , Mode :
---Pks EventColumn[index=0,columnType=3,columnName=GROUP_ID,columnValue=1,isNull=false,isKey=true,isUpdate=true] EventColumn[index=1,columnType=1111,columnName=STAFF_ID,columnValue=1,isNull=false,isKey=true,isUpdate=true] EventColumn[index=2,columnType=1111,columnName=STAFF_ROLE,columnValue=1,isNull=false,isKey=true,isUpdate=true] ---oldPks ---Columns EventColumn[index=3,columnType=91,columnName=UPDATE_TIME,columnValue=2017-03-05 00:00:00,isNull=false,isKey=false,isUpdate=true] EventColumn[index=4,columnType=1111,columnName=UPDATE_STAFF_ID,columnValue=1,isNull=false,isKey=false,isUpdate=true] ---Sql merge /+ use_nl(a b)/ into ucr_order_mgr.tf_m_staff_group a using (select ? as UPDATE_TIME , ? as UPDATE_STAFF_ID , ? as GROUP_ID , ? as STAFF_ID , ? as STAFF_ROLE from dual) b on (a.GROUP_ID=b.GROUP_ID and a.STAFF_ID=b.STAFF_ID and a.STAFF_ROLE=b.STAFF_ROLE) when matched then update set a.UPDATE_TIME=b.UPDATE_TIME , a.UPDATE_STAFF_ID=b.UPDATE_STAFF_ID when not matched then insert (a.UPDATE_TIME , a.UPDATE_STAFF_ID , a.GROUP_ID , a.STAFF_ID , a.STAFF_ROLE ) values (b.UPDATE_TIME , b.UPDATE_STAFF_ID , b.GROUP_ID , b.STAFF_ID , b.STAFF_ROLE ) Caused by: com.alibaba.otter.node.etl.load.exception.LoadException: com.alibaba.otter.node.etl.load.exception.LoadException: org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [merge /+ use_nl(a b)/ into ucr_order_mgr.tf_m_staff_group a using (select ? as UPDATE_TIME , ? as UPDATE_STAFF_ID , ? as GROUP_ID , ? as STAFF_ID , ? as STAFF_ROLE from dual) b on (a.GROUP_ID=b.GROUP_ID and a.STAFF_ID=b.STAFF_ID and a.STAFF_ROLE=b.STAFF_ROLE) when matched then update set a.UPDATE_TIME=b.UPDATE_TIME , a.UPDATE_STAFF_ID=b.UPDATE_STAFF_ID when not matched then insert (a.UPDATE_TIME , a.UPDATE_STAFF_ID , a.GROUP_ID , a.STAFF_ID , a.STAFF_ROLE ) values (b.UPDATE_TIME , b.UPDATE_STAFF_ID , b.GROUP_ID , b.STAFF_ID , b.STAFF_ROLE )]; SQL state [null]; error code [17004]; Invalid column type; nested exception is java.sql.SQLException: Invalid column type 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:603) at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:812) at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:868) at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction$DbLoadWorker$2.doInTransaction(DbLoadAction.java:621) at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:130) at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction$DbLoadWorker.doCall(DbLoadAction.java:613) at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction$DbLoadWorker.call(DbLoadAction.java:541) at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction.doTwoPhase(DbLoadAction.java:458) at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction.doLoad(DbLoadAction.java:275) at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction.load(DbLoadAction.java:161) at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction$$FastClassByCGLIB$$d932a4cb.invoke() at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:191) at org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:618) at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction$$EnhancerByCGLIB$$80fd23c2.load() at com.alibaba.otter.node.etl.load.loader.db.DataBatchLoader$2.call(DataBatchLoader.java:198) at com.alibaba.otter.node.etl.load.loader.db.DataBatchLoader$2.call(DataBatchLoader.java:189) at java.util.concurrent.FutureTask.run(FutureTask.java:262) at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471) at java.util.concurrent.FutureTask.run(FutureTask.java:262) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) at java.lang.Thread.run(Thread.java:745) Caused by: java.sql.SQLException: Invalid column type at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112) at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146) at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:208) at oracle.jdbc.driver.OraclePreparedStatement.setObjectCritical(OraclePreparedStatement.java:9231) at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:8812) at oracle.jdbc.driver.OraclePreparedStatement.setObject(OraclePreparedStatement.java:9285) at org.apache.commons.dbcp.DelegatingPreparedStatement.setObject(DelegatingPreparedStatement.java:166) at org.apache.commons.dbcp.DelegatingPreparedStatement.setObject(DelegatingPreparedStatement.java:166) at org.springframework.jdbc.core.StatementCreatorUtils.setValue(StatementCreatorUtils.java:370) at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValueInternal(StatementCreatorUtils.java:217) at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValue(StatementCreatorUtils.java:164) at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction$DbLoadWorker.doPreparedStatement(DbLoadAction.java:801) at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction$DbLoadWorker.access$800(DbLoadAction.java:513) at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction$DbLoadWorker$2$1.setValues(DbLoadAction.java:624) at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:816) at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:1) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:587) ... 21 more • PairId: 2 , TableId: 10 , EventType : I , Time : 1488720096000 • Consistency : , Mode :
---Pks EventColumn[index=0,columnType=3,columnName=GROUP_ID,columnValue=1,isNull=false,isKey=true,isUpdate=true] EventColumn[index=1,columnType=1111,columnName=STAFF_ID,columnValue=1,isNull=false,isKey=true,isUpdate=true] EventColumn[index=2,columnType=1111,columnName=STAFF_ROLE,columnValue=1,isNull=false,isKey=true,isUpdate=true] ---oldPks ---Columns EventColumn[index=3,columnType=91,columnName=UPDATE_TIME,columnValue=2017-03-05 00:00:00,isNull=false,isKey=false,isUpdate=true] EventColumn[index=4,columnType=1111,columnName=UPDATE_STAFF_ID,columnValue=1,isNull=false,isKey=false,isUpdate=true] ---Sql merge /+ use_nl(a b)/ into ucr_order_mgr.tf_m_staff_group a using (select ? as UPDATE_TIME , ? as UPDATE_STAFF_ID , ? as GROUP_ID , ? as STAFF_ID , ? as STAFF_ROLE from dual) b on (a.GROUP_ID=b.GROUP_ID and a.STAFF_ID=b.STAFF_ID and a.STAFF_ROLE=b.STAFF_ROLE) when matched then update set a.UPDATE_TIME=b.UPDATE_TIME , a.UPDATE_STAFF_ID=b.UPDATE_STAFF_ID when not matched then insert (a.UPDATE_TIME , a.UPDATE_STAFF_ID , a.GROUP_ID , a.STAFF_ID , a.STAFF_ROLE ) values (b.UPDATE_TIME , b.UPDATE_STAFF_ID , b.GROUP_ID , b.STAFF_ID , b.STAFF_ROLE ) Caused by: com.alibaba.otter.node.etl.load.exception.LoadException: org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [merge /+ use_nl(a b)/ into ucr_order_mgr.tf_m_staff_group a using (select ? as UPDATE_TIME , ? as UPDATE_STAFF_ID , ? as GROUP_ID , ? as STAFF_ID , ? as STAFF_ROLE from dual) b on (a.GROUP_ID=b.GROUP_ID and a.STAFF_ID=b.STAFF_ID and a.STAFF_ROLE=b.STAFF_ROLE) when matched then update set a.UPDATE_TIME=b.UPDATE_TIME , a.UPDATE_STAFF_ID=b.UPDATE_STAFF_ID when not matched then insert (a.UPDATE_TIME , a.UPDATE_STAFF_ID , a.GROUP_ID , a.STAFF_ID , a.STAFF_ROLE ) values (b.UPDATE_TIME , b.UPDATE_STAFF_ID , b.GROUP_ID , b.STAFF_ID , b.STAFF_ROLE )]; SQL state [null]; error code [17004]; Invalid column type; nested exception is java.sql.SQLException: Invalid column type 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:603) at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:812) at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:868) at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction$DbLoadWorker$2.doInTransaction(DbLoadAction.java:621) at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:130) at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction$DbLoadWorker.doCall(DbLoadAction.java:613) at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction$DbLoadWorker.call(DbLoadAction.java:541) at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction.doTwoPhase(DbLoadAction.java:458) at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction.doLoad(DbLoadAction.java:275) at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction.load(DbLoadAction.java:161) at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction$$FastClassByCGLIB$$d932a4cb.invoke() at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:191) at org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:618) at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction$$EnhancerByCGLIB$$80fd23c2.load() at com.alibaba.otter.node.etl.load.loader.db.DataBatchLoader$2.call(DataBatchLoader.java:198) at com.alibaba.otter.node.etl.load.loader.db.DataBatchLoader$2.call(DataBatchLoader.java:189) at java.util.concurrent.FutureTask.run(FutureTask.java:262) at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471) at java.util.concurrent.FutureTask.run(FutureTask.java:262) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) at java.lang.Thread.run(Thread.java:745) Caused by: java.sql.SQLException: Invalid column type at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112) at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146) at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:208) at oracle.jdbc.driver.OraclePreparedStatement.setObjectCritical(OraclePreparedStatement.java:9231) at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:8812) at oracle.jdbc.driver.OraclePreparedStatement.setObject(OraclePreparedStatement.java:9285) at org.apache.commons.dbcp.DelegatingPreparedStatement.setObject(DelegatingPreparedStatement.java:166) at org.apache.commons.dbcp.DelegatingPreparedStatement.setObject(DelegatingPreparedStatement.java:166) at org.springframework.jdbc.core.StatementCreatorUtils.setValue(StatementCreatorUtils.java:370) at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValueInternal(StatementCreatorUtils.java:217) at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValue(StatementCreatorUtils.java:164) at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction$DbLoadWorker.doPreparedStatement(DbLoadAction.java:801) at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction$DbLoadWorker.access$800(DbLoadAction.java:513) at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction$DbLoadWorker$2$1.setValues(DbLoadAction.java:624) at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:816) at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:1) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:587) ... 21 more • PairId: 2 , TableId: 10 , EventType : I , Time : 1488720096000 • Consistency : , Mode :
---Pks EventColumn[index=0,columnType=3,columnName=GROUP_ID,columnValue=1,isNull=false,isKey=true,isUpdate=true] EventColumn[index=1,columnType=1111,columnName=STAFF_ID,columnValue=1,isNull=false,isKey=true,isUpdate=true] EventColumn[index=2,columnType=1111,columnName=STAFF_ROLE,columnValue=1,isNull=false,isKey=true,isUpdate=true] ---oldPks ---Columns EventColumn[index=3,columnType=91,columnName=UPDATE_TIME,columnValue=2017-03-05 00:00:00,isNull=false,isKey=false,isUpdate=true] EventColumn[index=4,columnType=1111,columnName=UPDATE_STAFF_ID,columnValue=1,isNull=false,isKey=false,isUpdate=true] ---Sql merge /+ use_nl(a b)/ into ucr_order_mgr.tf_m_staff_group a using (select ? as UPDATE_TIME , ? as UPDATE_STAFF_ID , ? as GROUP_ID , ? as STAFF_ID , ? as STAFF_ROLE from dual) b on (a.GROUP_ID=b.GROUP_ID and a.STAFF_ID=b.STAFF_ID and a.STAFF_ROLE=b.STAFF_ROLE) when matched then update set a.UPDATE_TIME=b.UPDATE_TIME , a.UPDATE_STAFF_ID=b.UPDATE_STAFF_ID when not matched then insert (a.UPDATE_TIME , a.UPDATE_STAFF_ID , a.GROUP_ID , a.STAFF_ID , a.STAFF_ROLE ) values (b.UPDATE_TIME , b.UPDATE_STAFF_ID , b.GROUP_ID , b.STAFF_ID , b.STAFF_ROLE )
建表语句如下： MySQL： CREATE TABLE tf_m_staff_group ( GROUP_ID decimal(8,0) NOT NULL, STAFF_ID varchar(20) NOT NULL, STAFF_ROLE varchar(10) NOT NULL, UPDATE_TIME datetime NOT NULL, UPDATE_STAFF_ID varchar(20) NOT NULL, PRIMARY KEY (GROUP_ID,STAFF_ID,STAFF_ROLE) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; Oracle： CREATE TABLE tf_m_staff_group ( GROUP_ID NUMBER(22) NOT NULL, STAFF_ID NVARCHAR2(40) NOT NULL, STAFF_ROLE NVARCHAR2(20) NOT NULL, UPDATE_TIME date NOT NULL, UPDATE_STAFF_ID NVARCHAR2(40) NOT NULL, PRIMARY KEY (GROUP_ID,STAFF_ID,STAFF_ROLE) ); insert into tf_m_staff_group values (1, '1', '1', '2017-03-05', '1'); 有些表可以同步，有些表报错，基本都是这种 SQL state [null]; error code [17004]; Invalid column type，不清楚这是什么原因，求大侠解惑？
原提问者GitHub用户 westboy2000
写回答
-
找到原因了：oracle的表字段类型，用的是NVARCHAR2，转换的时候报错。改为VARCHAR2以后就正常了。
原回答者GitHub用户westboy2000
2023-06-21 17:40:10赞同 展开评论 打赏
版权声明：本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
相关电子书
更多
