java.sql.SQLException: ORA-24816: 在实际的 LONG 或 LOB 列之后提供了扩展的非 LONG 绑定数据
我是 execl 导入 出现 这样的报错,因为导入 当中有 clob 类型。之前 导入也是正常,没有报错,不知今天导入突然来这样的错误。
刚开始我第一想法是 检查 字段长度,检查每个字段都没有超出数据库设定的长度。
看了其它人的文章 说
原因是在最后insert到oracle表的时候,类型为 LONG 或 LOB (我的就是clob类型)的列,意思 CLOB 后面 还有其它类型 如 (varchar2,date,int 等)
其它人解决办法:在拼接insert语句的时候,要将clob字段放在最后。
原先sql 写法:answer clob 类型,这个就是后面的字段类型为varchar2 (standardQuestion classifyId )
···
insert into demo_table
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="id != null">
id,
</if>
<if test="answer != null">
answer,
</if>
<if test="classifyId != null">
classify_id,
</if>
<if test="standardQuestion != null">
standard_question,
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
<if test="id != null">
#{id},
</if>
<if test="answer != null">
#{answer},
</if>
<if test="classifyId != null">
#{classifyId},
</if>
<if test="standardQuestion != null">
#{standardQuestion},
</if>
</trim>
···
以下解决方法任何一种都可以:
第1种方式.改造sql 语法:
···
insert into demo_table
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="id != null">
id,
</if>
<if test="classifyId != null">
classify_id,
</if>
<if test="standardQuestion != null">
standard_question,
</if>
<if test="answer != null">
answer,
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
<if test="id != null">
#{id},
</if>
<if test="classifyId != null">
#{classifyId},
</if>
<if test="standardQuestion != null">
#{standardQuestion},
</if>
<if test="answer != null">
#{answer},
</if>
</trim>
···
第2种方式.
修改 resultMap <result column="answer" property="answer" jdbcType="CLOB" javaType="java.lang.String" typeHandler="com.ylz.springboot.modules.common.pojo.OracleClobTypeHandler"/> 修改 sql 加入 begin 和 ;end; begin insert into demo_table <trim prefix="(" suffix=")" suffixOverrides=","> <if test="id != null"> id, </if> <if test="answer != null"> answer, </if> <if test="classifyId != null"> classify_id, </if> <if test="standardQuestion != null"> standard_question, </if> </trim> <trim prefix="values (" suffix=")" suffixOverrides=","> <if test="id != null"> #{id}, </if> <if test="answer != null"> #{answer}, </if> <if test="classifyId != null"> #{classifyId}, </if> <if test="standardQuestion != null"> #{standardQuestion}, </if> </trim> ;end;
新增类
package com.ylz.springboot.modules.common.pojo; import oracle.sql.CLOB; import org.apache.ibatis.type.JdbcType; import org.apache.ibatis.type.TypeHandler; import java.sql.CallableStatement; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; /** * @author lhh * @Date 2019/11/14 13:53 */ public class OracleClobTypeHandler implements TypeHandler<Object> { @Override public void setParameter(PreparedStatement preparedStatement, int i, Object o, JdbcType jdbcType) throws SQLException { CLOB clob = CLOB.empty_lob(); clob.setString(1, (String) o); preparedStatement.setClob(i, clob); } @Override public Object getResult(ResultSet resultSet, String s) throws SQLException { CLOB clob = (CLOB) resultSet.getClob(s); return (clob == null || clob.length() == 0) ? null : clob.getSubString((long) 1, (int) clob.length()); } @Override public Object getResult(ResultSet resultSet, int i) throws SQLException { return null; } @Override public Object getResult(CallableStatement callableStatement, int i) throws SQLException { return null; } }