String literal too long while inserting a XML into XMLTYPE Column in Oracle

Question

i have a text XML with more than 100K Characters , I am trying to write a PLSQL Block to insert this XML into an XMLTYPE Oracle Table. Below is my PLSQL Block and it wont allow to insert the xml and getting error saying string literal is too long because Oracle's SQL can only handle up to 4000 characters . I cannot change the datatype in Oracle. How i can insert this XML ?

Table Column is REQUEST_XML    XMLTYPE

I was trying like this but it seems that also not working.

INSERT INTO XYZ.ABC(ID,REQUEST_XML) values(123,yourXmlStr);

PLSQL is below,

DECLARE
yourXmlStr   xmltype := xmltype('<DRIVEResponse TimeZone="EDT">
<Condition1  ActionStep="ABCABC"  /> // This can be more than 100K Characters
</DRIVEResponse>');
BEGIN
  INSERT INTO XYZ.ABC(ID,REQUEST_XML) values(123,XMLTYPE.CREATEXML(yourXmlStr));
COMMIT;
END;

What is your table structure? Are you sure the CreateXML is not truncating it instead of the table itself? — Brad
Update the question . And no CreateXML not truncating it, i still see 100K Characters — VKP

eaolson eaolson · Accepted Answer · 2021-03-17T23:19:14

You're creating an xmltype twice, which is unnecessary. Your source data is a string literal (anything between two single quotes), which in PL/SQL has a maximum length of 4000 characters (or 32767, see MAX_STRING_SIZE). Fortunately, the xmltype constructor can also operate on a clob. You don't say where the source data is coming from, so I'm not sure of the best way to construct that:

declare
    l_clob    clob;
    l_xml     xmltype;
begin
    l_clob := '<DRIVEResponse TimeZone="EDT">';
    l_clob := l_clob || '<Condition1  ActionStep="ABCABC"  />';
    ... repeat in chunks of less than 32768 characters ...
    l_clob := l_clob || '</DRIVEResponse>';
    l_xml := xmltype.createxml( l_clob );
    
    INSERT INTO XYZ.ABC(ID,REQUEST_XML) values(123, l_xml);
end;

String literal too long while inserting a XML into XMLTYPE Column in Oracle

1 Answers