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

Question

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?

What version of Oracle are you using, and how large is the XML in the particular INSERT? — Tim Biegeleisen
– Tim Biegeleisen, Commented Jul 1, 2016 at 14:14
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
– SeaSide, Commented Jul 1, 2016 at 14:27

Nick S · Accepted Answer · 2016-07-01 17:05:15Z

6

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

answered Jul 1, 2016 at 17:05

Nick S

4342 silver badges11 bronze badges

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

2 Comments

SeaSide Over a year ago

Nick S, sorry for the late reply. This with PL/SQL is the only solution. My question is how can I pass the parameters to this PL/SQL procedure and XML parameter assign to this declare parameter in_xml_value varchar2(32767)? PL/SQL not my strong side. Thank you for your answer.

SeaSide Over a year ago

Nick S, after a while of testing I succeeded to resolve my problem with your method using PL/SQL. Thank you a lot!

Mottor · Accepted Answer · 2016-07-01 14:13:31Z

0

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

answered Jul 1, 2016 at 14:13

Mottor

1,9583 gold badges16 silver badges29 bronze badges

1 Comment

SeaSide Over a year ago

Everything is ok with the statement. I know because I used it with the other table but XML document of that table is smaller than this one and that xml is inserting into table perfectly. I think that the problem is with the size of XML document.

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related