CODEWITHSUNDEEP
javaspring-bootmybatis
Rollsbean
Rollsbean

Reputation: 743

Mybatis batch insert exception:ORA-00933: SQL command not properly ended

My project uses Springboot and Mybatis, I use batch insert grammer to save data and I used an oracle database

<sql id="site_columns">
    ID, SITE_URL, BRM_HGSSITE_ID, BRM_SITE_STATUS, BPS_HGSSITE_ID, SITE_STATUS, OP_HGSSITE_ID, BILLING_PLATFORM, BLIS_EFFECTIVE_FROM,
    BLIS_EFFECTIVE_TO, BRM_EFFECTIVE_FROM, ISSUE_TYPE, COMMENTS, CSM_FIRST_NAME, CSM_LAST_NAME, CSM_EMAIL, CREATE_TIME
</sql>

<insert id="batchSaveVerifiedSitesFromFile" parameterType="siteSummaryInfoBean">
    INSERT INTO MR_VERIFIED_SITE_LIST(<include refid="site_columns"/>) VALUES
    <foreach collection="list" item="site" separator=",">
        (MR_VERIFIED_SITE_LIST_SEQ.nextval, #{site.webexurl}, #{site.brm_hgssiteid}, #{site.brm_sitestatus},
        #{site.bps_hgssiteid}, #{site.sitestatus},
        #{site.op_hgssiteid}, #{site.billingplatform,jdbcType=VARCHAR}, #{site.blis_effectiveFrom,jdbcType=DATE},
        #{site.blis_effectiveTo,jdbcType=DATE}, #{site.brm_effectiveFrom,jdbcType=DATE},
        #{site.issueType,jdbcType=VARCHAR}, #{site.comments,jdbcType=VARCHAR},
        #{site.csmfirstname,jdbcType=VARCHAR}, #{site.csmlastname,jdbcType=VARCHAR},
        #{site.csmemail,jdbcType=VARCHAR},
        greatest(nvl(#{site.blis_effectiveTo,jdbcType=DATE},sysdate),
        nvl(#{site.brm_effectiveFrom,jdbcType=DATE},sysdate)))
    </foreach>
</insert>

Upvotes: 0

Views: 2381

Answers (2)

erdos
erdos

Reputation: 3528

Oracle does not support multi-row insert statements.

You can write an insert into ... select ... union all select ... statement as seen in a different answer.

This way in your foreach loop the separator will be union all and the loop body will be select ... from dual.

Upvotes: 1

Andreea Craciun
Andreea Craciun

Reputation: 310

The insert statement should be inside the foreach.

For example overwrite $sql variable in each loop in the code itself.

Upvotes: 0

Related Questions