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
– Brad, Commented Mar 17, 2021 at 18:17
Update the question . And no CreateXML not truncating it, i still see 100K Characters — VKP
– VKP, Commented Mar 17, 2021 at 18:22

eaolson · Accepted Answer · 2021-03-18 03:23:01Z

2

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;

edited Mar 18, 2021 at 3:23

answered Mar 17, 2021 at 23:19

eaolson

15.2k7 gold badges46 silver badges62 bronze badges

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

4 Comments

VKP Over a year ago

So the Chunk of 4000 characters has to created in my service layer before calling Oracle ?

eaolson Over a year ago

That's going to depend. You could create a clob and use it in your insert statement with ... values(123, XMLTYPE.CREATEXML(:mylob)). I'd argue this would be a good candidate for a stored procedure taking a clob as a parameter. I'm not sure about using clobs as bind variables; I get the impression that can be a bit finicky.

Jon Heller Over a year ago

@eaolson The maximum VARCHAR2 variable size in PL/SQL is 32767 bytes, per the PL/SQL Program Limits. That value is necessarily the same as the maximum size of a VARCHAR2 in SQL - which you are right is either 4000 or 32767.

eaolson Over a year ago

To be really pedantic, that's a string literal, not a varchar2. They're slightly different, though I admit the difference here is largely academic. String literals use blank-padded comparison, like chars, for example.

Collectives™ on Stack Overflow

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

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related