ORA-01704:在Oracle XMLTYPE列类型中插入XML文档时，字符串文字太长'错误'

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:

当我试图从Oracle表中的SQL表中插入数据时，我得到了这个错误“ORA-01704: string literal too long”。在Oracle表中，有一个列具有XMLTYPE列类型。在创建表时，我指定了如下XML列:

  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?

除了这一列之外，在它之前我还有另外23列，当我将insert语句XML列排除在外时，insert正在传递。XML列包含来自表的所有其他23列的XML格式的数据。我是否应该在XML列中为长度添加一些额外的规范?

2 个解决方案

#1

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.

我的猜测是，您将XML作为文本传递给insert语句。Oracle的SQL只能处理高达4000个字符的文字。否则，您需要使用bind变量并将其以块形式传递。也可以使用PL/SQL。

For example, this should work without issue because the literal

例如，这应该没有问题，因为文字

<MyMessage>Meeesaaagee</MyMessage>

is only 34 characters:

只有34个字符:

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)

但是如果你有:Meeesaaagee(+ 3976个额外字符)

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

您将得到ORA-01704:字符串文字错误太长。

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

使用w/o PL/SQL代码并使用绑定变量，您必须与应用程序开发人员进行对话。它在Oracle之外(在我的知识之外)。

#2

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

错误在哪里?insert语句。我在这里没有看到任何问题:

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

#1