How to insert large XML string into an Oracle Table?

Question

I want to insert a large XML string into my Table. My table is test_id(xml_column XMLType).

When I insert the values, it returns 'string literal too long' error. I surf on the net about this, everyone says to change the datatype to CLOB. But i want to store the same datatype(XMLType).

Is there any possibilities to do that. Can anyone help me to fix this.

Here is my code:

insert into TEST_ID(xml_column) values('<root>
  <DatabaseLog>
    <DatabaseLogID>1</DatabaseLogID>
    <PostTime>2012-03-14T13:14:18.803</PostTime>
...
<object>StoreSurveySchemaCollection</object>
  </DatabaseLog>
</root>');

The error code is:

PLS-00172: string literal too long

Thanks in advice.

Sylvain Leroux · Accepted Answer · 2014-12-22 11:27:16Z

5

As of Oracle 11g, the maximum size for an XMLTYPE value is 2G/4G depending your character set. This is the same as the CLOB type. But, the issue here is not with the content being too large for the XMLTYPE. It is the litteral VARCHAR you use in your query that is too long. The error message is quite clear with that matter:

PLS-00172: string literal too long

A solution (not a very elegant one though) is to break your string is several pieces, assigning to a CLOB variable:

xmlDoc CLOB := 'xml chunk #1';
...
xmlDoc := xmlDoc || 'xml chunk #2';
xmlDoc := xmlDoc || 'xml chunk #3';

...
insert into TEST_ID(xml_column) values(xmlDoc);

edited Dec 22, 2014 at 11:27

answered Dec 22, 2014 at 11:21

Sylvain Leroux

52.3k8 gold badges114 silver badges136 bronze badges

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

2 Comments

Kenny Steegmans Over a year ago

You can use yourXML.GETCLOBVAL() to get a CLOB from an xmltype.

Pratik Rawlekar Over a year ago

Sylvain, How can we make this operation dynamically when we don't know size of the json string and want to insert in XMLTYPE?

Collectives™ on Stack Overflow

How to insert large XML string into an Oracle Table?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related