CODEWITHSUNDEEP
stringoracle-databasexmltype
user3807736
user3807736

Reputation:

How to insert large XML string into an Oracle Table?

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.

Upvotes: 2

Views: 5682

Answers (1)

Sylvain Leroux
Sylvain Leroux

Reputation: 52000

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);

Upvotes: 5

Related Questions