ORA-01704: string literal too long 'Error when inserting XML document in Oracle XMLTYPE column type'

2
votes

I am getting this error 'ORA-01704: string literal too long' while I am trying to insert data from SQL table in Oracle table. In my Oracle table I have a column with XMLTYPE column type. When I was creating table I specified XML column like this:

  CREATE TABLE REPORTS (
       ...
    XML XMLTYPE NULL );

Apart from this column, before it I have others 23 columns and when I exclude from insert statement XML column, insert is passing. XML column contain a data in XML format of all others 23 columns from table. Whether I should add some additional specification in my XML column for length or something other?

2
xmloracle
What error do you get? Please add that to the question. - DocRattie
What version of Oracle are you using, and how large is the XML in the particular INSERT? - Tim Biegeleisen
I am using this version 'Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production' and XML size is 4,39 KB (4.497 bytes) - SeaSide

2 Answers

5
votes

My guess is you are passing the XML as a literal to the insert statement. Oracle's SQL can only handle up to 4000 characters in a literal. Otherwise you need to use bind variables and pass it in chunks. Or you can use PL/SQL.

For example, this should work without issue because the literal 

<MyMessage>Meeesaaagee</MyMessage>

is only 34 characters:

CREATE TABLE TEST_REPORTS
(
   ID            NUMBER,
   DESCRIPTION   VARCHAR2 (50),
   XML           XMLTYPE NULL
);

INSERT INTO TEST_REPORTS (ID, DESCRIPTION, XML)
     VALUES (1, 'BLAH BLAH', XMLTYPE.CREATEXML ('<MyMessage>Meeesaaagee</MyMessage>'));

COMMIT;

But if you had: Meeesaaagee (+ 3976 extra characters)

You will get the ORA-01704: string literal too long error.

You could try:

DECLARE
    in_xml_value varchar2(32767);
BEGIN
    in_xml_value := '<MyMessage>MeeesaaageeBLAHBLAHBLAH<--repeat--></MyMessage>';
    INSERT INTO TEST_REPORTS (ID, DESCRIPTION, XML)
     VALUES (1, 'BLAH BLAH', XMLTYPE.CREATEXML (in_xml_value);
    commit;
END;
/

Do do it w/o PL/SQL code and to use bind variables, well you would have to talk with an application developer. It is outside of Oracle (and outside of my knowledge).

0
votes

Where is the error? Show the insert statement. I do not see any problem here:

CREATE TABLE TEST_REPORTS
(
   ID            NUMBER,
   DESCRIPTION   VARCHAR2 (50),
   XML           XMLTYPE NULL
);

INSERT INTO TEST_REPORTS (ID, DESCRIPTION, XML)
     VALUES (1, 'BLAH BLAH', XMLTYPE.CREATEXML ('<MyMessage>Meeesaaagee</MyMessage>'));