我要在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的方式。
dbms_lob.loadclobfromfile
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;