Oracle Sql Developer“字符串文字太长”错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5486889/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle Sql Developer "string literal too long" error
提问by wcm
I have the following SQL that I would like to run in Oracle SQL Developer against an Oracle 10g server:
我想在 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;
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.
My strings will occasionally be much longer than 4000 characters. Any ideas about how I can get around this problem?
我的字符串有时会超过 4000 个字符。关于如何解决这个问题的任何想法？
采纳答案by Paul Perigny
You will need to use a CLOB as the input to XMLTYPE() instead of a VARCHAR.
您将需要使用 CLOB 作为输入到 XMLTYPE() 而不是 VARCHAR。
Using either dbms_lob.loadclobfromfileto load the xml from a file, or by breaking up the xml into 32000 character chunks and appending to the CLOB.
使用dbms_lob.loadclobfromfile从文件加载 xml，或将 xml 分解为 32000 个字符块并附加到 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;
回答by a_horse_with_no_name
You can't get around this with "plain" SQL. (But I'd be glad to be proven wrong)
使用“普通”SQL 无法解决此问题。（但我很高兴被证明是错误的）
You will need some kind of programming language (e.g. Java, Stored Procedure) to deal with this.
您将需要某种编程语言（例如 Java、存储过程）来处理此问题。
An alternative is to upload the XML data into a table (can be done with SQL*Loader) and the use the column values in your query.
另一种方法是将 XML 数据上传到表中（可以使用 SQL*Loader 完成）并在查询中使用列值。
This is one of the limitations of Oracle that is really driving me nuts. 20 years ago this might have been somewhat acceptable, but nowadays...
这是 Oracle 真正让我抓狂的限制之一。20 年前，这可能在某种程度上是可以接受的，但现在......
回答by Farid Z
You can use sql workaround using insert/updates where each part if less than 4000 chars.
如果每个部分少于 4000 个字符，您可以使用插入/更新使用 sql 解决方法。
1 Do the insert as an insert with first part is the sql literal up to 4000 chars 2 Do the additional parts as an update concatenating the previous parts with the next part where the next part is up to 4000 chars 3 Repeat step 2 until all the large sql literal is updated.
1 将插入作为插入，第一部分是最多 4000 个字符的 sql 文字 2 将附加部分作为更新，将前一部分与下一部分连接起来，其中下一部分最多 4000 个字符 3 重复步骤 2，直到所有大 sql 文字已更新。
Example,
例子，
Insert into
test_large_literal (col1, col2)
values
(<key val>, <first part of large sql literal>);
update
test_large_literal
set
col2 = col2 || <second part large sql literal>
where
col1 = <key val>;
...
...
update
test_large_literal
set
col2 = col2 || <last part large sql literal>
where
col1 = <key val>;
回答by Gary Myers
Where does that great big chunk of XML come from ? I assume you are not typing it in.
这么大的一大块 XML 来自哪里？我假设你没有输入它。
Generally I'd look at a program that reads the source and turns it into a CLOB. That might be a perl/python/whatever script on a client, or it might be a server side routine that pulls the value from a web-server.
通常，我会查看一个读取源代码并将其转换为 CLOB 的程序。这可能是客户端上的 perl/python/whatever 脚本，也可能是从 Web 服务器中提取值的服务器端例程。
回答by DCookie
A possible workaround is to use PL/SQL blocks:
一种可能的解决方法是使用 PL/SQL 块：
DECLARE
xml VARCHAR2(32000) :=
'<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>';
CURSOR C (p1 INTEGER, p2 INTEGER) IS
SELECT * FROM (
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(xml).extract('/theRange/theRow'))))
)
WHERE age BETWEEN p1 AND p2;
BEGIN
FOR R IN C (30,35) LOOP
dbms_output.put_line(R.First||', '||R.Last||', '||R.Age);
END LOOP;
END;
(Completely untested)
（完全未经测试）
EDIT:
编辑：
As an insert, you could try:
作为插入，您可以尝试：
DECLARE
xml VARCHAR2(32000) :=
'<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>';
BEGIN
INSERT INTO temp_table(last,first,age)
SELECT last, first, age FROM (
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(xml).extract('/theRange/theRow'))))
)
WHERE age BETWEEN 30 AND 35;
END;
