ORA-01704:字符串文字太长'在Oracle XMLTYPE列类型中插入XML文档时出错' [英] ORA-01704: string literal too long 'Error when inserting XML document in Oracle XMLTYPE column type'

问题描述

当我尝试从Oracle表中的SQL表插入数据时，出现此错误"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:

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

除此列外，在我还有其他23列之前，当我从插入语句XML列中排除时，insert正在传递. XML列包含表中所有其他23列的XML格式的数据.是否应该在XML列中添加一些其他规范来说明长度或其他?

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?

推荐答案

我的猜测是您要将XML作为文字传递给insert语句. Oracle的SQL在一个文字中最多只能处理4000个字符.否则，您需要使用绑定变量并将其分块传递.或者，您可以使用PL/SQL.

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> 

只有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;

但是，如果您有: Meeesaaagee(另加3976个字符)

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

您将收到ORA-01704:字符串文字太长的错误.

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

您可以尝试:

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;
/

不使用PL/SQL代码并使用绑定变量来执行此操作，那么您将不得不与应用程序开发人员联系.它在Oracle之外(也不在我的知识范围之内).

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

这篇关于ORA-01704:字符串文字太长'在Oracle XMLTYPE列类型中插入XML文档时出错'的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！