oracle批量插入clob字段

ORA-01461: can bind a LONG value only for insert into a LONG column

Type handler was null on parameter mapping for property '__frch_uid_0'

当从 dual 中取数据时，会将 clob 对象的字段转为 Long 型。

ojdbc14.jar，对日期和clob类型有处理，查出来的是对象。ojdbc6.jar查询结果是字符串。

方式一：不可以。

原因：select 值 form dual，其中值的长度不能大于4000，如果是中文，长度不能大于2000，否则报错。

INSERT INTO TEST_CLOB(id,clob1,clob2)
select A.* from
<foreach collection="list" separator="UNION ALL" item="item" open="(" close=")">
select
#{id,jdbcType=VARCHAR} ID,
#{item.clob1,jdbcType=VARCHAR} clob1,
#{item.clob2,jdbcType=VARCHAR} clob2 from DUAL
</foreach>

方式二：使用merge into，不可以

<insert id="saveDataByBatch" parameterType="java.util.Map">
merge into s_contract_account sca using
<foreach collection="list" item="info" index="index"
separator="union all" open="(" close=")">
select
#{info.contractNo,jdbcType=VARCHAR} as contractNo,
#{info.contractName,jdbcType=VARCHAR} as contractName,

#{info.signRemark,jdbcType=CLOB} as signRemark from dual
</foreach> d
on (sca.contract_no = d.contractNo)
when matched then
update set sca.contract_name = d.contractName
when not matched then
insert
(id,
contract_no,
contract_name,
sign_remark,
create_user,
create_time)
values
(
SEQ_CONTRACT_ACCOUNT.NEXTVAL,
d.contractNo,
d.contractName,
d.signRemark,
#{createuser,jdbcType=VARCHAR},
sysdate
)
</insert>

方式二：使用mybatis批量插入，这种方式是可以的，但要指定jdbcType=Clob

<insert id="batchInsert"
begin
<foreach collection="list" item="item" index="index" separator=";">
insert into ${tableName}
(
id,
error_detail,
create_date
)
values(
#{item.id},
#{item.errorDetail},
#{item.createDate}
)
</foreach>
;end;
</insert>

使用insert all

<insert id="batchUserRole" useGeneratedKeys="false">
   insert all
   <foreach item="item" index="index" collection="list">
      into sys_user_role(user_id, role_id) values (#{item.userId，jdbcType=NUMERIC},#{item.roleId,jdbcType=NUMERIC}
 </foreach>
   SELECT 1 FROM DUAL
</insert>

来自Stack Overflow：

<insert id="save..." parameterType="...DTO">
    <selectKey keyProperty="id" resultType="long" order="BEFORE">
        SELECT SEQ.nextVal FROM DUAL
    </selectKey>
    insert into MYTABLE(
        ID,
        ...,
        PDF
    ) values (
        #{id, jdbcType=VARCHAR},
        ...,
        #{tcPdf, jdbcType=BLOB},
    )
</insert>



SELECT ACTIVITY_TRACKER_ID,
  MAX(DECODE(PROTOCOL_NUMBER, NULL, TO_CHAR(PROTOCOL_NUMBER1), TO_CHAR(PROTOCOL_NUMBER))) PROTOCOL_NUMBER,
  MAX(DECODE(QUESTION_ID, '1', DBMS_LOB.SUBSTR(COMMENT_NOTES,4000,1))) RESEARCHPURPOSE1,
   MAX(DECODE(QUESTION_ID, '1', DBMS_LOB.SUBSTR(COMMENT_NOTES,4000,4001))) RESEARCHPURPOSE2
FROM ......

批量插入：

select * from table  where id in
<foreach collection="yourList" index="index" item="item" open="(" separator="," close=")">
    #{item.id}       
</foreach> 

单条插入：

<insert id="addDutyPost" parameterType="dp">
    insert into sds_duty_post(id,title,content,creator,create_date)
    values (SEQ_SDS_DUTY_POST.NEXTVAL,#{title,jdbcType=VARCHAR},#{content,jdbcType=CLOB},#{creator,jdbcType=VARCHAR},sysdate)
</insert>

以上来源于网络