ORA-01461: 仅能绑定要插入 LONG 列的 LONG 值

转载地址：https://www.jianshu.com/p/05786ff67ab7
在操作数据库的时候，讲一个数值保存到一个VARCHAR(2000)的字段里面，出现了下面的错误：仅能绑定要插入 LONG 列的 LONG 值。

以下是我的错误异常日志

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)

初步怀疑是插入的值大于该字段数据类型约束的长度导致。因为这个异常是我在保存一个字段信息的时候出现的，保存的信息过大，超过了我在数据库指定的长度2000

在网上找到了解决方法，这里记录下:
如果是varchar2类型(最大长度为4000字节)的，当长度超过2000–4000(最大值)之间的时候，Oracle会自动将该字段值转为long型的，插入操作失败。
解决办法是：将此字段的类型改为clob或者blob类型；
但是将oracle数据库表中的varchar2类型直接改成clob类型会失败，解决办法如下：new 一个Sql view，执行下列代码：

alter table table_name modify col_name long;
alter table table_name modify col_name clob;

在进行此操作之前需要先备份表的数据，然后清空表中的数据，再修改表的字段属性，最后将数据导入即可。
注：clob不需指定长度。
1、插入到字符串长度大于4000字节。
2、插入到表中的记录其中一个是long类型、还有其他实际长度大于2000个字节（如果是 UTF-8，则是1333个字节）；或者是插入的记录中有两个或两个以上长度大于2000字节的字符串 。
用以下SQL可以查出有可能引起ORA-01461错误的表：

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;

关于错误的其他原因：
当向ORACLE数据库中插入或更新数据时，报错“ORA-01461: 仅可以为插入 LONG 列的 LONG 值赋值”，可能有以下几种原因：
1、插入到字符串长度大于4000字节。
2、插入到表中的记录的某个字段数据的实际长度大于2000个字节（如果是UTF-8，则是1333个字节）；或者是插入的记录中有两个或两个以上长度大于2000字节的字符串。
3、数据库与客户端的JDBC 驱动不匹配。对于UTF-8或欧洲的某些字符集，oracle在存储时，对于一个字符需要2个或3个字节的存储空间，虽然表定义中为 varchar2(4000)，但是其实该字段的data_length为其2倍或3倍长。这种情况下oracle会把data_length长度超过 4000的当做LONG型处理，你的表中有两个这样的字段，插入数据时相当于同时操作2个LONG字段。