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

Question

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>

Your error related to Oracle Database, please, provide executed query — user6100520
– user6100520, Commented Sep 29, 2017 at 8:06

erdos · Accepted Answer · 2017-09-29 10:42:13Z

1

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.

answered Sep 29, 2017 at 10:42

erdos

3,6132 gold badges22 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Andreea Craciun · Accepted Answer · 2017-09-29 08:31:36Z

0

The insert statement should be inside the foreach.

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

answered Sep 29, 2017 at 8:31

Andreea Craciun

3101 silver badge6 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related