ORA-01461 when SQLXML field contains XML longer than 4096 characters.

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

ORA-01461 when SQLXML field contains XML longer than 4096 characters.

Bryan Donaldson
I have a table
{
pk number(38)
parent_pk number(38),
sequence number(4)
some_text  CLOB,
some_xml XMLType
}

oracle 12.c database

using mybatis 3.4.4
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="tablemapper" >
    <resultMap id="BaseResultMap" type="table_entity" >
        <result column="PK" property="pk" jdbcType="VARCHAR" />
        <result column="SEQUENCE" property="sequence" jdbcType="BIGINT" />
        <result column=SOME_TEXT" property="someText" jdbcType="CLOB" typeHandler="org.apache.ibatis.type.ClobTypeHandler" />
        <result column="SOME_XML" property="someXml" jdbcType="SQLXML"/>
    </resultMap>

    <insert id="insertTransaction" parameterType="table_entity" >
        <selectKey keyProperty="pk" resultType="string" order="BEFORE">
                 select TO_CHAR(ATABLE_SEQ.nextval) as pk from dual
        </selectKey>
        INSERT INTO ATABLE(PK,SEQUENCE,SOME_TEXT, SOME_XML)
        VALUES(TO_NUMBER(#{pk, jdbcType=VARCHAR}),
        #{sequence, jdbcType=BIGINT},
        #{someText, jdbcType=CLOB,typeHandler=org.apache.ibatis.type.ClobTypeHandler},
        #{someXml, jdbcType=SQLXML} )
    </insert>
</mapper>


jdbc verison 12.1.0.2.0

when I insert into the table and don't insert to some_xml, the statement works.
when I insert into the table with someXml < 4k, the statement works.
when I insert into the table with someXml > 4k, ORA-01461 is reported and the insert fails.



--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: ORA-01461 when SQLXML field contains XML longer than 4096 characters.

Guy Rouillier-2

and 


Looks like you need to wrap your literal with XMLType() function.

--
Guy Rouillier

------ Original Message ------
From: "Bryan Donaldson" <[hidden email]>
To: "mybatis-user" <[hidden email]>
Sent: 8/3/2017 5:26:35 PM
Subject: ORA-01461 when SQLXML field contains XML longer than 4096 characters.

I have a table
{
pk number(38)
parent_pk number(38),
sequence number(4)
some_text  CLOB,
some_xml XMLType
}

oracle 12.c database

using mybatis 3.4.4
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="tablemapper" >
    <resultMap id="BaseResultMap" type="table_entity" >
        <result column="PK" property="pk" jdbcType="VARCHAR" />
        <result column="SEQUENCE" property="sequence" jdbcType="BIGINT" />
        <result column=SOME_TEXT" property="someText" jdbcType="CLOB" typeHandler="org.apache.ibatis.type.ClobTypeHandler" />
        <result column="SOME_XML" property="someXml" jdbcType="SQLXML"/>
    </resultMap>

    <insert id="insertTransaction" parameterType="table_entity" >
        <selectKey keyProperty="pk" resultType="string" order="BEFORE">
                 select TO_CHAR(ATABLE_SEQ.nextval) as pk from dual
        </selectKey>
        INSERT INTO ATABLE(PK,SEQUENCE,SOME_TEXT, SOME_XML)
        VALUES(TO_NUMBER(#{pk, jdbcType=VARCHAR}),
        #{sequence, jdbcType=BIGINT},
        #{someText, jdbcType=CLOB,typeHandler=org.apache.ibatis.type.ClobTypeHandler},
        #{someXml, jdbcType=SQLXML} )
    </insert>
</mapper>


jdbc verison 12.1.0.2.0

when I insert into the table and don't insert to some_xml, the statement works.
when I insert into the table with someXml < 4k, the statement works.
when I insert into the table with someXml > 4k, ORA-01461 is reported and the insert fails.



--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.

Virus-free. www.avast.com

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: ORA-01461 when SQLXML field contains XML longer than 4096 characters.

Bryan Donaldson
i started while using Mybatic 3.2.2 using a clob in the result map 

        <result column="SOME_XML" property="someXml"  jdbcType="CLOB" typeHandler="org.apache.ibatis.type.ClobTypeHandler"/>

and using XMLType in the insert - but it exhibited the same problem. 

People encountering that issue recommended dropping back to JDBC and using createSQLXML and PreparedStatement to resolve the issue.  when I realized that MyBatis 3.4.4 supported SQLXML type directly i upgraded that (and dependencies) hoping that the issue would be resolved.   It was not.   Thus the post. 



On Friday, August 4, 2017 at 2:28:37 AM UTC-4, Guy Rouillier wrote:
See <a href="https://docs.oracle.com/database/121/ADXDB/xdb04cre.htm#GUID-0FDD8ABE-F94B-42CB-B135-93BC67225737" style="font-size:12pt" target="_blank" rel="nofollow" onmousedown="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fdocs.oracle.com%2Fdatabase%2F121%2FADXDB%2Fxdb04cre.htm%23GUID-0FDD8ABE-F94B-42CB-B135-93BC67225737\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNHFZYonYAdkW-n5EyEeM5ez5vEJpw&#39;;return true;" onclick="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fdocs.oracle.com%2Fdatabase%2F121%2FADXDB%2Fxdb04cre.htm%23GUID-0FDD8ABE-F94B-42CB-B135-93BC67225737\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNHFZYonYAdkW-n5EyEeM5ez5vEJpw&#39;;return true;">https://docs.oracle.com/database/121/ADXDB/xdb04cre.htm#GUID-0FDD8ABE-F94B-42CB-B135-93BC67225737

and 

<a href="https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9524181800346018444" target="_blank" rel="nofollow" onmousedown="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fasktom.oracle.com%2Fpls%2Fapex%2Ff%3Fp%3D100%3A11%3A0%3A%3A%3A%3AP11_QUESTION_ID%3A9524181800346018444\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNGquvn6IwN4JESvSs9q3cc2Tc4N3g&#39;;return true;" onclick="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fasktom.oracle.com%2Fpls%2Fapex%2Ff%3Fp%3D100%3A11%3A0%3A%3A%3A%3AP11_QUESTION_ID%3A9524181800346018444\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNGquvn6IwN4JESvSs9q3cc2Tc4N3g&#39;;return true;">https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9524181800346018444

Looks like you need to wrap your literal with XMLType() function.

--
Guy Rouillier

------ Original Message ------
From: "Bryan Donaldson" <<a href="javascript:" target="_blank" gdf-obfuscated-mailto="JISvIkWrBQAJ" rel="nofollow" onmousedown="this.href=&#39;javascript:&#39;;return true;" onclick="this.href=&#39;javascript:&#39;;return true;">bryando...@...>
To: "mybatis-user" <<a href="javascript:" target="_blank" gdf-obfuscated-mailto="JISvIkWrBQAJ" rel="nofollow" onmousedown="this.href=&#39;javascript:&#39;;return true;" onclick="this.href=&#39;javascript:&#39;;return true;">mybati...@...>
Sent: 8/3/2017 5:26:35 PM
Subject: ORA-01461 when SQLXML field contains XML longer than 4096 characters.

I have a table
{
pk number(38)
parent_pk number(38),
sequence number(4)
some_text  CLOB,
some_xml XMLType
}

oracle 12.c database

using mybatis 3.4.4
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//<a href="http://mybatis.org//DTD" target="_blank" rel="nofollow" onmousedown="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fmybatis.org%2F%2FDTD\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNHjp-E8UL0TmmS7A0V54yMfbtfEkA&#39;;return true;" onclick="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fmybatis.org%2F%2FDTD\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNHjp-E8UL0TmmS7A0V54yMfbtfEkA&#39;;return true;">mybatis.org//DTD Mapper 3.0//EN" "<a href="http://mybatis.org/dtd/mybatis-3-mapper.dtd" target="_blank" rel="nofollow" onmousedown="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fmybatis.org%2Fdtd%2Fmybatis-3-mapper.dtd\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNFpJ-PGZSCiEykIm86cAd_bUTvMAQ&#39;;return true;" onclick="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fmybatis.org%2Fdtd%2Fmybatis-3-mapper.dtd\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNFpJ-PGZSCiEykIm86cAd_bUTvMAQ&#39;;return true;">http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="tablemapper" >
    <resultMap id="BaseResultMap" type="table_entity" >
        <result column="PK" property="pk" jdbcType="VARCHAR" />
        <result column="SEQUENCE" property="sequence" jdbcType="BIGINT" />
        <result column=SOME_TEXT" property="someText" jdbcType="CLOB" typeHandler="org.apache.ibatis.type.ClobTypeHandler" />
        <result column="SOME_XML" property="someXml" jdbcType="SQLXML"/>
    </resultMap>

    <insert id="insertTransaction" parameterType="table_entity" >
        <selectKey keyProperty="pk" resultType="string" order="BEFORE">
                 select TO_CHAR(ATABLE_SEQ.nextval) as pk from dual
        </selectKey>
        INSERT INTO ATABLE(PK,SEQUENCE,SOME_TEXT, SOME_XML)
        VALUES(TO_NUMBER(#{pk, jdbcType=VARCHAR}),
        #{sequence, jdbcType=BIGINT},
        #{someText, jdbcType=CLOB,typeHandler=org.apache.ibatis.type.ClobTypeHandler},
        #{someXml, jdbcType=SQLXML} )
    </insert>
</mapper>


jdbc verison 12.1.0.2.0

when I insert into the table and don't insert to some_xml, the statement works.
when I insert into the table with someXml < 4k, the statement works.
when I insert into the table with someXml > 4k, ORA-01461 is reported and the insert fails.



--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to <a href="javascript:" target="_blank" gdf-obfuscated-mailto="JISvIkWrBQAJ" rel="nofollow" onmousedown="this.href=&#39;javascript:&#39;;return true;" onclick="this.href=&#39;javascript:&#39;;return true;">mybatis-user...@googlegroups.com.
For more options, visit <a href="https://groups.google.com/d/optout" target="_blank" rel="nofollow" onmousedown="this.href=&#39;https://groups.google.com/d/optout&#39;;return true;" onclick="this.href=&#39;https://groups.google.com/d/optout&#39;;return true;">https://groups.google.com/d/optout.

<a href="https://www.avast.com/sig-email?utm_medium=email&amp;utm_source=link&amp;utm_campaign=sig-email&amp;utm_content=emailclient&amp;utm_term=icon" target="_blank" rel="nofollow" onmousedown="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fwww.avast.com%2Fsig-email%3Futm_medium%3Demail%26utm_source%3Dlink%26utm_campaign%3Dsig-email%26utm_content%3Demailclient%26utm_term%3Dicon\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNGdgn6XHtGdGX0_ztl7zsznn9rycA&#39;;return true;" onclick="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fwww.avast.com%2Fsig-email%3Futm_medium%3Demail%26utm_source%3Dlink%26utm_campaign%3Dsig-email%26utm_content%3Demailclient%26utm_term%3Dicon\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNGdgn6XHtGdGX0_ztl7zsznn9rycA&#39;;return true;"> Virus-free. <a href="https://www.avast.com/sig-email?utm_medium=email&amp;utm_source=link&amp;utm_campaign=sig-email&amp;utm_content=emailclient&amp;utm_term=link" style="color:#4453ea" target="_blank" rel="nofollow" onmousedown="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fwww.avast.com%2Fsig-email%3Futm_medium%3Demail%26utm_source%3Dlink%26utm_campaign%3Dsig-email%26utm_content%3Demailclient%26utm_term%3Dlink\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNEBTW1oVO7X9WxlATGseHnM8Gn-HQ&#39;;return true;" onclick="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fwww.avast.com%2Fsig-email%3Futm_medium%3Demail%26utm_source%3Dlink%26utm_campaign%3Dsig-email%26utm_content%3Demailclient%26utm_term%3Dlink\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNEBTW1oVO7X9WxlATGseHnM8Gn-HQ&#39;;return true;">www.avast.com
<a href="#em7499dfe7-8423-436a-a8bb-091fe0e68b87@asus_DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2" width="1" height="1" rel="nofollow" onmousedown="this.href=&#39;#em7499dfe7-8423-436a-a8bb-091fe0e68b87@asus_DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2&#39;;return true;" onclick="this.href=&#39;#em7499dfe7-8423-436a-a8bb-091fe0e68b87@asus_DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2&#39;;return true;">

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: ORA-01461 when SQLXML field contains XML longer than 4096 characters.

Iwao AVE!
Hi Bryan,

This thread might be helpful.
https://groups.google.com/d/msg/mybatis-user/t4DVv8G5UYs/nZxrTI0FJ3kJ

Regards,
Iwao

2017-08-04 22:11 GMT+09:00 Bryan Donaldson <[hidden email]>:
i started while using Mybatic 3.2.2 using a clob in the result map 

        <result column="SOME_XML" property="someXml"  jdbcType="CLOB" typeHandler="org.apache.ibatis.type.ClobTypeHandler"/>

and using XMLType in the insert - but it exhibited the same problem. 

People encountering that issue recommended dropping back to JDBC and using createSQLXML and PreparedStatement to resolve the issue.  when I realized that MyBatis 3.4.4 supported SQLXML type directly i upgraded that (and dependencies) hoping that the issue would be resolved.   It was not.   Thus the post. 



On Friday, August 4, 2017 at 2:28:37 AM UTC-4, Guy Rouillier wrote:

and 


Looks like you need to wrap your literal with XMLType() function.

--
Guy Rouillier

------ Original Message ------
From: "Bryan Donaldson" <[hidden email]>
To: "mybatis-user" <[hidden email]>
Sent: 8/3/2017 5:26:35 PM
Subject: ORA-01461 when SQLXML field contains XML longer than 4096 characters.

I have a table
{
pk number(38)
parent_pk number(38),
sequence number(4)
some_text  CLOB,
some_xml XMLType
}

oracle 12.c database

using mybatis 3.4.4
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="tablemapper" >
    <resultMap id="BaseResultMap" type="table_entity" >
        <result column="PK" property="pk" jdbcType="VARCHAR" />
        <result column="SEQUENCE" property="sequence" jdbcType="BIGINT" />
        <result column=SOME_TEXT" property="someText" jdbcType="CLOB" typeHandler="org.apache.ibatis.type.ClobTypeHandler" />
        <result column="SOME_XML" property="someXml" jdbcType="SQLXML"/>
    </resultMap>

    <insert id="insertTransaction" parameterType="table_entity" >
        <selectKey keyProperty="pk" resultType="string" order="BEFORE">
                 select TO_CHAR(ATABLE_SEQ.nextval) as pk from dual
        </selectKey>
        INSERT INTO ATABLE(PK,SEQUENCE,SOME_TEXT, SOME_XML)
        VALUES(TO_NUMBER(#{pk, jdbcType=VARCHAR}),
        #{sequence, jdbcType=BIGINT},
        #{someText, jdbcType=CLOB,typeHandler=org.apache.ibatis.type.ClobTypeHandler},
        #{someXml, jdbcType=SQLXML} )
    </insert>
</mapper>


jdbc verison 12.1.0.2.0

when I insert into the table and don't insert to some_xml, the statement works.
when I insert into the table with someXml < 4k, the statement works.
when I insert into the table with someXml > 4k, ORA-01461 is reported and the insert fails.



--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email]om.
For more options, visit https://groups.google.com/d/optout.

Virus-free. www.avast.com

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.
Loading...