DevOps, CI/CD and Automation

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

What is default size of XMLType column?

765321Apr 14 2010 — edited Apr 14 2010

I have a table with 1 raw type and 1 XMLType column. In the XMLType column I am inserting following XML,

<ocaStatus xmlns="http://xmlbeans.apache.org/ocastatus">
<status>
<statusCode>990</statusCode>
<statusDateclass="sql-date">2010-01-19</statusDate>
<userId>OcaNimsAcf</userId>
<comment>DocumentnotinNIMS</comment>
</status>
<status>
<statusCode>990</statusCode>
<statusDateclass="sql-date">2010-01-19</statusDate>
<userId>OcaNimsAcf</userId>
<comment>DocumentnotinNIMS</comment>
</status>
<status>
<statusCode>501</statusCode>
<statusDateclass="sql-date">2008-09-16</statusDate>
<userId>oca_scheduled_job</userId>
</status>
<status>
<statusCode>501</statusCode>
<statusDateclass="sql-date">2008-09-16</statusDate>
<userId>oca_scheduled_job</userId>
</status>
<status>
<statusCode>501</statusCode>
<statusDateclass="sql-date">2008-09-16</statusDate>
<userId>oca_scheduled_job</userId>
</status>
<status>
<statusCode>501</statusCode>
<statusDateclass="sql-date">2008-08-29</statusDate>
<userId>oca_scheduled_job</userId>
</status>
<status>
<statusCode>501</statusCode>
<statusDateclass="sql-date">2008-08-29</statusDate>
<userId>oca_scheduled_job</userId>
</status>
<status>
<statusCode>501</statusCode>
<statusDateclass="sql-date">2008-08-28</statusDate>
<userId>oca_scheduled_job</userId>
</status>
<status>
<statusCode>501</statusCode>
<statusDateclass="sql-date">2008-08-28</statusDate>
<userId>oca_scheduled_job</userId>
</status>
<status>
<statusCode>501</statusCode>
<statusDateclass="sql-date">2008-08-27</statusDate>
<userId>oca_scheduled_job</userId>
</status>
<status>
<statusCode>501</statusCode>
<statusDateclass="sql-date">2008-08-26</statusDate>
<userId>oca_scheduled_job</userId>
</status>
<status>
<statusCode>501</statusCode>
<statusDateclass="sql-date">2008-08-25</statusDate>
<userId>oca_scheduled_job</userId>
</status>
<status>
<statusCode>501</statusCode>
<statusDateclass="sql-date">2008-08-22</statusDate>
<userId>oca_scheduled_job</userId>
</status>
<status>
<statusCode>501</statusCode>
<statusDateclass="sql-date">2008-08-22</statusDate>
<userId>oca_scheduled_job</userId>
</status>
<status>
<statusCode>501</statusCode>
<statusDateclass="sql-date">2008-08-22</statusDate>
<userId>oca_scheduled_job</userId>
</status>
<status>
<statusCode>501</statusCode>
<statusDateclass="sql-date">2008-08-22</statusDate>
<userId>oca_scheduled_job</userId>
</status>
<status>
<statusCode>501</statusCode>
<statusDateclass="sql-date">2008-08-22</statusDate>
<userId>oca_scheduled_job</userId>
</status>
<status>
<statusCode>501</statusCode>
<statusDateclass="sql-date">2008-08-21</statusDate>
<userId>oca_scheduled_job</userId>
</status>
<status>
<statusCode>501</statusCode>
<statusDateclass="sql-date">2008-08-21</statusDate>
<userId>oca_scheduled_job</userId>
</status>
<status>
<statusCode>501</statusCode>
<statusDateclass="sql-date">2008-08-21</statusDate>
<userId>oca_scheduled_job</userId>
</status>
<status>
<statusCode>501</statusCode>
<statusDateclass="sql-date">2008-08-21</statusDate>
<userId>oca_scheduled_job</userId>
</status>
<status>
<statusCode>501</statusCode>
<statusDateclass="sql-date">2008-08-21</statusDate>
<userId>oca_scheduled_job</userId>
</status>
<status>
<statusCode>501</statusCode>
<statusDateclass="sql-date">2008-08-20</statusDate>
<userId>oca_scheduled_job</userId>
</status>
<status>
<statusCode>501</statusCode>
<statusDateclass="sql-date">2008-08-20</statusDate>
<userId>oca_scheduled_job</userId>
</status>
<status>
<statusCode>501</statusCode>
<statusDateclass="sql-date">2008-08-20</statusDate>
<userId>oca_scheduled_job</userId>
</status>
<status>
<statusCode>306</statusCode>
<statusDateclass="sql-date">2008-04-21</statusDate>
<userId>u0075970</userId>
</status>
<status>
<statusCode>301</statusCode>
<statusDateclass="sql-date">2008-02-05</statusDate>
<userId>DMS_WORKFLOW</userId>
<comment></comment>
</status>
<status>
<statusCode>990</statusCode>
<statusDateclass="sql-date">2010-01-17</statusDate>
<userId>OcaNimsAcf</userId>
<comment>Comparedfornimsacf</comment>
</status>
<status>
<statusCode>501</statusCode>
<statusDateclass="sql-date">2008-09-16</statusDate>
<userId>oca_scheduled_job</userId>
</status>
<status>
<statusCode>501</statusCode>
<statusDateclass="sql-date">2008-09-16</statusDate>
<userId>oca_scheduled_job</userId>
</status>
<status>
<statusCode>501</statusCode>
<statusDateclass="sql-date">2008-09-16</statusDate>
<userId>oca_scheduled_job</userId>
</status>
<status>
<statusCode>501</statusCode>
<statusDateclass="sql-date">2008-08-29</statusDate>
<userId>oca_scheduled_job</userId>
</status>
<status>
<statusCode>501</statusCode>
<statusDateclass="sql-date">2008-08-29</statusDate>
<userId>oca_scheduled_job</userId>
</status>
<status>
<statusCode>501</statusCode>
<statusDateclass="sql-date">2008-08-28</statusDate>
<userId>oca_scheduled_job</userId>
</status>
<status>
<statusCode>501</statusCode>
<statusDateclass="sql-date">2008-08-28</statusDate>
<userId>oca_scheduled_job</userId>
</status>
<status>
<statusCode>501</statusCode>
<statusDateclass="sql-date">2008-08-27</statusDate>
<userId>oca_scheduled_job</userId>
</status>
<status>
<statusCode>501</statusCode>
<statusDateclass="sql-date">2008-08-26</statusDate>
<userId>oca_scheduled_job</userId>
</status>
<status>
<statusCode>501</statusCode>
<statusDateclass="sql-date">2008-08-25</statusDate>
<userId>oca_scheduled_job</userId>
</status>
<status>
<statusCode>501</statusCode>
<statusDateclass="sql-date">2008-08-22</statusDate>
<userId>oca_scheduled_job</userId>
</status>
<status>
<statusCode>501</statusCode>
<statusDateclass="sql-date">2008-08-22</statusDate>
<userId>oca_scheduled_job</userId>
</status>
<status>
<statusCode>501</statusCode>
<statusDateclass="sql-date">2008-08-22</statusDate>
<userId>oca_scheduled_job</userId>
</status>
<status>
<statusCode>501</statusCode>
<statusDateclass="sql-date">2008-08-22</statusDate>
<userId>oca_scheduled_job</userId>
</status>
<status>
<statusCode>501</statusCode>
<statusDateclass="sql-date">2008-08-22</statusDate>
<userId>oca_scheduled_job</userId>
</status>
<status>
<statusCode>501</statusCode>
<statusDateclass="sql-date">2008-08-21</statusDate>
<userId>oca_scheduled_job</userId>
</status>
<status>
<statusCode>501</statusCode>
<statusDateclass="sql-date">2008-08-21</statusDate>
<userId>oca_scheduled_job</userId>
</status>
<status>
<statusCode>501</statusCode>
<statusDateclass="sql-date">2008-08-21</statusDate>
<userId>oca_scheduled_job</userId>
</status>
<status>
<statusCode>501</statusCode>
<statusDateclass="sql-date">2008-08-21</statusDate>
<userId>oca_scheduled_job</userId>
</status>
<status>
<statusCode>501</statusCode>
<statusDateclass="sql-date">2008-08-21</statusDate>
<userId>oca_scheduled_job</userId>
</status>
<status>
<statusCode>501</statusCode>
<statusDateclass="sql-date">2008-08-20</statusDate>
<userId>oca_scheduled_job</userId>
</status>
<status>
<statusCode>501</statusCode>
<statusDateclass="sql-date">2008-08-20</statusDate>
<userId>oca_scheduled_job</userId>
</status>
<status>
<statusCode>501</statusCode>
<statusDateclass="sql-date">2008-08-20</statusDate>
<userId>oca_scheduled_job</userId>
</status>
<status>
<statusCode>306</statusCode>
<statusDateclass="sql-date">2008-04-21</statusDate>
<userId>u0075970</userId>
</status>
<status>
<statusCode>301</statusCode>
<statusDateclass="sql-date">2008-02-05</statusDate>
<userId>DMS_WORKFLOW</userId>
<comment></comment>
</status>
</ocaStatus>

The xml is very long. For inserting into the table I am using xmlparse() function to get XMLType.
While doing this I am getting ORA-01704: string literal too long error. It says can accommodate at the most 4000 characters.
I guess XMLType is stored as a CLOB by default.So it should allow more than 4000 characters.
How can I store xml with more than 4000 characters.

Thanks.

This post has been answered by odie_63 on Apr 14 2010

Jump to Answer

odie_63

Answer

XMLParse is a SQL function, so you can't use a string literal longer than 4000 chars.

Instead, you may declare a PL/SQL VARCHAR2 variable, which can hold up to 32k characters, and then use the XMLType constructor :

DECLARE

 xmlstr VARCHAR2(32767) := '<xml string here>';
 xmldoc xmltype;
 
BEGIN

 xmldoc := xmltype(xmlstr);
 INSERT INTO your_table(xml_data) VALUES(xmldoc);

END;

For strings longer than 32k, you'll probably have to load it from a file.

Marked as Answer by 765321 · Sep 27 2020

1 - 1

Locked Post

New comments cannot be posted to this locked post.

Locked on May 12 2010

Added on Apr 14 2010

#of, #pl-sql-xml-programming, #xml

1 comment

2,540 views

DevOps, CI/CD and Automation

What is default size of XMLType column?

Comments

Post Details