Oracle Sql Developer“字符串文字太长"错误 [英] Oracle Sql Developer "string literal too long" error

问题描述

我要在Oracle SQL Developer中针对Oracle 10g服务器运行以下SQL:

I have the following SQL that I would like to run in Oracle SQL Developer against an Oracle 10g server:

WITH openedXml AS (
  SELECT extractvalue(column_value, '/theRow/First') FIRST,
         extractvalue(column_value, '/theRow/Last') LAST,
         to_number(extractvalue(column_value, '/theRow/Age')) Age
    FROM TABLE(XMLSequence(XMLTYPE('
  <theRange>
    <theRow><First>Bob</First><Last>Smith</Last><Age>30</Age></theRow>
    <theRow><First>Sue</First><Last>Jones</Last><Age>34</Age></theRow>
...
...
...
    <theRow><First>Tom</First><Last>Anderson</Last><Age>39</Age></theRow>
    <theRow><First>Ali</First><Last>Grady</Last><Age>45</Age></theRow>
  </theRange>
  ').extract('/theRange/theRow')))
)
SELECT *
FROM openedxml
WHERE age BETWEEN 30 AND 35;

当我尝试运行它时，出现以下错误:

When I attempt to run it I get the following error:

Error at Command Line:1 Column:0 Error report: SQL Error: ORA-01704: string literal too long
01704. 00000 -  "string literal too long"
*Cause:    The string literal is longer than 4000 characters.
*Action:   Use a string literal of at most 4000 characters.
           Longer values may only be entered using bind variables.

我的字符串有时会长于4000个字符.关于如何解决这个问题有什么想法吗?

My strings will occasionally be much longer than 4000 characters. Any ideas about how I can get around this problem?

推荐答案

您将需要使用CLOB作为XMLTYPE()的输入，而不是VARCHAR.

You will need to use a CLOB as the input to XMLTYPE() instead of a VARCHAR.

使用dbms_lob.loadclobfromfile从文件中加载xml，或者通过将xml分成32000个字符块并将其附加到CLOB.

Using either dbms_lob.loadclobfromfile to load the xml from a file, or by breaking up the xml into 32000 character chunks and appending to the CLOB.

DECLARE
   xmlClob CLOB;
BEGIN
/* Build Clob here */

WITH openedXml AS (
  SELECT extractvalue(column_value, '/theRow/First') FIRST,
         extractvalue(column_value, '/theRow/Last') LAST,
         to_number(extractvalue(column_value, '/theRow/Age')) Age
    FROM TABLE(XMLSequence(XMLTYPE(xmlClob).extract('/theRange/theRow')))
)
SELECT *
FROM openedxml
WHERE age BETWEEN 30 AND 35;
END;

这篇关于Oracle Sql Developer“字符串文字太长"错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！