如何在 Oracle 的 CONTAINS 运算符中解决 text_query 的 4000 个字符限制？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3116046/
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
How to get around 4000 characters limitation of text_query in Oracle's CONTAINS operator?
提问by hko19
In Oracle, the full text search syntax of Contains operatoris:
在 Oracle 中，Contains 运算符的全文搜索语法为：
CONTAINS(
[schema.]column,
text_query VARCHAR2
[,label NUMBER]) RETURN NUMBER;
which means the text_query can not be more than 4000 characters long or an error will occur. I repeatedly have text_query longer than 4000 characters long in many cases. How would you, as an Oracle expert, suggest to get around such limitation if possible?
这意味着 text_query 的长度不能超过 4000 个字符，否则会发生错误。在许多情况下，我反复使用超过 4000 个字符的 text_query。如果可能，作为 Oracle 专家，您会如何建议绕过此类限制？
To further clarify the situation in which 4000 is easily reached is that if you combine many Contains Query Operatorsto construct your text_query, it is quite possible to exceed such 4000 characters limitation.
进一步说明容易达到 4000 的情况是，如果您组合许多 包含查询运算符来构建您的 text_query，则很有可能超过 4000 个字符的限制。
回答by APC
The 4000 character limit is not some arbitrary boundary: it is the maximum amount of VARCHAR2 characters that Oracle SQL can handle.
4000 个字符的限制不是任意边界：它是 Oracle SQL 可以处理的最大 VARCHAR2 字符数。
4000 characters is a lotof text. In English it's around 600 words, or an A4 page and a bit in a reasonable point font. There are not many applications I can think of which require searching for such large chunks of verbiage. Even colleges checking students' essays for plagiarism would operate at no more than the paragraph level.
4000 个字符是很多文本。在英语中，它大约有 600 个字，或者是 A4 纸，并且有一点点字体。我能想到的应用程序并不多，需要搜索如此大量的文字。即使是大学检查学生论文是否抄袭，也不会超过段落级别。
However, if you really have a situation in which matching on a scant 4000 characters generates false positives all you can do is split the query string into chunks and search on them. This means you have to use PL/SQL:
但是，如果您确实遇到过匹配不足 4000 个字符会产生误报的情况，您所能做的就是将查询字符串拆分为多个块并对其进行搜索。这意味着您必须使用 PL/SQL：
create or replace function big_search (p_search_text in varchar2)
return sys_refcursor
is
return_value sys_refcursor;
p_srch1 varchar2(4000);
p_srch2 varchar2(4000);
begin
dbms_output.put_line('search_length='||to_char(length(p_search_text)));
p_srch1 := substr(p_search_text, 1, 4000);
p_srch2 := substr(p_search_text, 4001, 4000);
open return_value for
select docname
, (score(1) + score(2))/2 as score
from t23
where contains ( text_column, p_srch1 , 1) != 0
and contains ( text_column, p_srch2 , 2) != 0;
return return_value;
end;
/
If you don't know the size of the search text beforehand, then you'll need to use dynamic SQL to assemble this. Note that passing null search terms to CONTAINS() will hurl DRG-50901: text query parser syntax error.
如果您事先不知道搜索文本的大小，那么您将需要使用动态 SQL 来组装它。请注意，将空搜索词传递给 CONTAINS() 将投掷DRG-50901: text query parser syntax error。
回答by Ophir Yoktan
The current version supports now a CLOB parameter
当前版本现在支持 CLOB 参数
CONTAINS(
[schema.]column,
text_query [VARCHAR2|CLOB]
[,label NUMBER])
RETURN NUMBER;
http://docs.oracle.com/cd/B28359_01/text.111/b28304/csql.htm#i997503
http://docs.oracle.com/cd/B28359_01/text.111/b28304/csql.htm#i997503
