我想在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;
