站长网 百科 Oracle Sql Developer“字符串文字太长”错误

Oracle Sql Developer“字符串文字太长”错误

我想在Oracle SQL Developer中针对Oracle 10g服务器运行以下SQL: WITH openedXml AS ( SELECT extractvalue(column_value,’/theRow/First’) FIRST,extractvalue(column_value,’/theRow/Last’) LAST,to_number(extractvalue(column_value,’/theRow/Age’))

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

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;

当我尝试运行它时,我收到以下错误:

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个字符.关于如何解决这个问题的任何想法?

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

使用dbms_lob.loadclobfromfile从文件加载xml,或者将xml拆分为32000个字符块并附加到CLOB.

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

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

本文来自网络,不代表站长网立场,转载请注明出处:https://www.tzzz.com.cn/html/baike/2021/0524/5425.html

作者: dawei

【声明】:站长网内容转载自互联网,其相关言论仅代表作者个人观点绝非权威,不代表本站立场。如您发现内容存在版权问题,请提交相关链接至邮箱:bqsm@foxmail.com,我们将及时予以处理。
联系我们

联系我们

0577-28828765

在线咨询: QQ交谈

邮箱: xwei067@foxmail.com

工作时间:周一至周五,9:00-17:30,节假日休息

返回顶部