在操作數(shù)據(jù)庫(kù)的時(shí)候，講一個(gè)數(shù)值保存到一個(gè)VARCHAR(2000)的字段里面，出現(xiàn)了下面的錯(cuò)誤：僅能綁定要插入 LONG 列的 LONG 值。
以下是我的錯(cuò)誤異常日志
org.springframework.jdbc.UncategorizedSQLException:
### Error updating database. Cause: java.sql.SQLException: ORA-01461: 僅能綁定要插入 LONG 列的 LONG 值
### The error may involve com.ytd.lineTrip.model.tripRecord.LineTripRecord.update-Inline
### The error occurred while setting parameters
### SQL: UPDATE W_LINE_TRIP_RECORD SET F_HAPPENTIME=?, F_STATIONNAME=?, F_EQUIPMENTNAME=?, F_EQUIPMENTTYPE=?, F_TESTNAME=?, F_ITEMNAME=?, F_EQUIPMENTNAME2=?, F_OLDID=?, F_VOLLEVEL=?, F_ISDELETE=?, F_RECORD=?, F_AREA=?, F_LINECODE=?, F_PHASE=?, F_LOSTLOAD=?, F_CONSUMER=?, F_RECLOSE=?, F_VIPCONSUMER=?, F_DESCRIBE=?, F_TRIPTIME=?, F_ONTIME=?, F_EVENTNAME=?, F_THEDISPATCHING=?, F_THEAREACOUNTY=?, F_SUBSTATION2=?, F_LINELENGTH=?, F_OUTAGETYPE=?, F_POWERFAILURETYPE=?, F_FAULTTYPE=?, F_MAINTENANCEDEPARTMEN=?, F_OUTAGECAUSE=?, F_LOWVOLTAGETALK=?, F_PROTECTIONACTION=?, F_ENERGIZATIONINFO=?, F_LLINEPATROLINFO=?, F_RECOVERYINFO=?, F_RECORDMAN=?, F_RECORDMANID=?, F_RECORDTIME=?, F_EFFECTSELECTRICITY=?, F_RECEIVETIME=?, F_FSRQ=?, F_FZDZT=?, F_THEBUS=?, F_LOADRECOVERED=?, F_RECOVEREDUSERNUM=?, F_EFFECTSUSERSIMPORTAN=?, F_IMPORTANTUSERSREPOWE=?, F_ONTRIP_RECEIVETIME=?, F_FYJZT=?, F_FYJRQ=?, F_SYNCSTATE=?, F_LINENAME=?, F_JBM=?, F_STATUS=?, F_SUBSTATION1=? WHERE ID=?
### Cause: java.sql.SQLException: ORA-01461: 僅能綁定要插入 LONG 列的 LONG 值
; uncategorized SQLException for SQL []; SQL state [72000]; error code [1461]; ORA-01461: 僅能綁定要插入 LONG 列的 LONG 值
; nested exception is java.sql.SQLException: ORA-01461: 僅能綁定要插入 LONG 列的 LONG 值
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.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:71)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:364)
at com.sun.proxy.$Proxy10.update(Unknown Source)
at org.mybatis.spring.SqlSessionTemplate.update(SqlSessionTemplate.java:250)
at com.hotent.core.db.GenericDao.update(GenericDao.java:329)
at com.hotent.core.service.GenericService.update(GenericService.java:62)
at com.ytd.lineTrip.service.tripRecord.LineTripRecordService.synchronizationToOms(LineTripRecordService.java:63)
at com.ytd.lineTrip.service.tripRecord.LineTripRecordService$$FastClassByCGLIB$$1afc51af.invoke(<generated>)
at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
at org.springframework.aop.framework.Cglib2AopProxy$CglibMethodInvocation.invokeJoinpoint(Cglib2AopProxy.java:688)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:90)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:621)
at com.ytd.lineTrip.service.tripRecord.LineTripRecordService$$EnhancerByCGLIB$$41c68730.synchronizationToOms(<generated>)
at com.ytd.oms.instancehandler.service.impl.OcsTzjl.receiveData(OcsTzjl.java:121)
at com.ytd.oms.soap.SOAPImpl.getData(SOAPImpl.java:56)
at com.ytd.oms.soap.SOAPSkeleton.getData(SOAPSkeleton.java:164)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.apache.axis.providers.java.RPCProvider.invokeMethod(RPCProvider.java:397)
at org.apache.axis.providers.java.RPCProvider.processMessage(RPCProvider.java:186)
at org.apache.axis.providers.java.JavaProvider.invoke(JavaProvider.java:323)
at org.apache.axis.strategies.InvocationStrategy.visit(InvocationStrategy.java:32)
at org.apache.axis.SimpleChain.doVisiting(SimpleChain.java:118)
at org.apache.axis.SimpleChain.invoke(SimpleChain.java:83)
at org.apache.axis.handlers.soap.SOAPService.invoke(SOAPService.java:454)
at org.apache.axis.server.AxisServer.invoke(AxisServer.java:281)
at org.apache.axis.transport.http.AxisServlet.doPost(AxisServlet.java:699)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:641)
at org.apache.axis.transport.http.AxisServletBase.service(AxisServletBase.java:327)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:722)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:225)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:169)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:168)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:98)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:927)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:407)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:999)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:565)
at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:307)
at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:895)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:918)
at java.lang.Thread.run(Thread.java:662)
初步懷疑是插入的值大于該字段數(shù)據(jù)類型約束的長(zhǎng)度導(dǎo)致。因?yàn)檫@個(gè)異常是我在保存一個(gè)字段信息的時(shí)候出現(xiàn)的，保存的信息過大，超過了我在數(shù)據(jù)庫(kù)指定的長(zhǎng)度2000
在網(wǎng)上找到了解決方法，這里記錄下:
如果是varchar2類型(最大長(zhǎng)度為4000字節(jié))的，當(dāng)長(zhǎng)度超過2000--4000(最大值)之間的時(shí)候，Oracle會(huì)自動(dòng)將該字段值轉(zhuǎn)為long型的，插入操作失敗。
解決辦法是：將此字段的類型改為clob或者blob類型；
但是將oracle數(shù)據(jù)庫(kù)表中的varchar2類型直接改成clob類型會(huì)失敗，解決辦法如下：new 一個(gè)Sql view，執(zhí)行下列代碼：
alter table table_name modify col_name long;
alter table table_name modify col_name clob;
在進(jìn)行此操作之前需要先備份表的數(shù)據(jù)，然后清空表中的數(shù)據(jù)，再修改表的字段屬性，最后將數(shù)據(jù)導(dǎo)入即可。
注：clob不需指定長(zhǎng)度。
1、插入到字符串長(zhǎng)度大于4000字節(jié)。
2、插入到表中的記錄其中一個(gè)是long類型、還有其他實(shí)際長(zhǎng)度大于2000個(gè)字節(jié)（如果是 UTF-8，則是1333個(gè)字節(jié)）；或者是插入的記錄中有兩個(gè)或兩個(gè)以上長(zhǎng)度大于2000字節(jié)的字符串 。
用以下SQL可以查出有可能引起ORA-01461錯(cuò)誤的表：
SELECT * FROM
(SELECT TABLE_NAME, OWNER, count(*) NUM
FROM DBA_TAB_COLUMNS
WHERE DATA_TYPE='LONG'
OR (( DATA_TYPE='VARCHAR2'
or DATA_TYPE='CHAR'
or DATA_TYPE='NVARCHAR2'
or DATA_TYPE='NCHAR')
AND DATA_LENGTH > 1333)
AND OWNER NOT IN
('SYS','SYSTEM','SH','OLAPSYS','MDSYS','WKSYS','ORDSYS','CTXSYS','ODM','XDB','WMSYS','APEX_030200','EXFSYS','SYSMAN','ORDDATA')
GROUP BY TABLE_NAME, OWNER)
WHERE NUM > 1;
關(guān)于錯(cuò)誤的其他原因：
當(dāng)向ORACLE數(shù)據(jù)庫(kù)中插入或更新數(shù)據(jù)時(shí)，報(bào)錯(cuò)“ORA-01461: 僅可以為插入 LONG 列的 LONG 值賦值”，可能有以下幾種原因：
1、插入到字符串長(zhǎng)度大于4000字節(jié)。
2、插入到表中的記錄的某個(gè)字段數(shù)據(jù)的實(shí)際長(zhǎng)度大于2000個(gè)字節(jié)（如果是UTF-8，則是1333個(gè)字節(jié)）；或者是插入的記錄中有兩個(gè)或兩個(gè)以上長(zhǎng)度大于2000字節(jié)的字符串。
3、數(shù)據(jù)庫(kù)與客戶端的JDBC 驅(qū)動(dòng)不匹配。對(duì)于UTF-8或歐洲的某些字符集，oracle在存儲(chǔ)時(shí)，對(duì)于一個(gè)字符需要2個(gè)或3個(gè)字節(jié)的存儲(chǔ)空間，雖然表定義中為 varchar2(4000)，但是其實(shí)該字段的data_length為其2倍或3倍長(zhǎng)。這種情況下oracle會(huì)把data_length長(zhǎng)度超過 4000的當(dāng)做LONG型處理，你的表中有兩個(gè)這樣的字段，插入數(shù)據(jù)時(shí)相當(dāng)于同時(shí)操作2個(gè)LONG字段。
