在 oracle 查询中使用长字符串（超过 4000）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7324607/
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
using long string(over 4000) in oracle query
提问by kralco626
I know that in sql varchar2 can only be around 4000.
我知道在 sql varchar2 中只能是 4000 左右。
I know that in oracle PL varchcar2 can be around 32000.
我知道在 oracle PL varchcar2 中可以是 32000 左右。
I have a varchar2 variable defined that is over 4000 characters long and I want to use it in a query. I don't want to insert the value into a table. The value is a dilimited string that I am parsing and inserting into a table with this query. This query works when the variable is less than 4000 characters long. Is there a way to make it work with up to 32000 characters?
我定义了一个超过 4000 个字符的 varchar2 变量，我想在查询中使用它。我不想将值插入表中。该值是一个分隔字符串，我正在使用此查询解析并插入到表中。当变量少于 4000 个字符时，此查询有效。有没有办法让它最多使用 32000 个字符？
create global temporary table t(single_element varchar(500),element_no number);
declare
--declared as 32767 but this string contains less than 4000 characters.
--This will work. If you expand the string to 32000 characters it will not work.
myvar varchar2(32767) := 'tcd1~#testmsg1^~tcd2~#testmsg2^~tcd3~#testmsg3^~tcd4~#testmsg4^~tcd1~#testmsg1^~tcd2~#testmsg2^~tcd3~#testmsg3^~tcd4~#testmsg4^~tcd1~#testmsg1^~tcd2~#testmsg2^~tcd3~#testmsg3^~tcd4~#testmsg4^~tcd1~#testmsg1^~tcd2~#testmsg2^~tcd3~#testmsg3^~tcd4~#testmsg4^~tcd1~#testmsg1^~tcd2~#testmsg2^~tcd3~#testmsg3^~tcd4~#testmsg4^~tcd1~#testmsg1^~tcd2~#testmsg2^~tcd3~#testmsg3^~tcd4~#testmsg4^~tcd1~#testmsg1^~tcd2~#testmsg2^~tcd3~#testmsg3^~tcd4~#testmsg4^~tcd1~#testmsg1^~tcd2~#testmsg2^~tcd3~#testmsg3^~tcd4~#testmsg4^~tcd1~#testmsg1^~tcd2~#testmsg2^~tcd3~#testmsg3^~tcd4~#testmsg4^~tcd1~#testmsg1^~tcd2~#testmsg2^~tcd3~#testmsg3^~tcd4~#testmsg4^~tcd1~#testmsg1^~tcd2~#testmsg2^~tcd3~#testmsg3^~tcd4~#testmsg4^~tcd1~#testmsg1^~tcd2~#testmsg2^~tcd3~#testmsg3^~tcd4~#testmsg4^~tcd1~#testmsg1^~tcd2~#testmsg2^~tcd3~#testmsg3^~tcd4~#testmsg4^~tcd1~#testmsg1^~tcd2~#testmsg2^~tcd3~#testmsg3^~tcd4~#testmsg4^~tcd1~#testmsg1^~tcd2~#testmsg2^~tcd3~#testmsg3^~tcd4~#testmsg4^~tcd1~#testmsg1^~tcd2~#testmsg2^~tcd3~#testmsg3^~tcd4~#testmsg4^~tcd1~#testmsg1^~tcd2~#testmsg2^~tcd3~#testmsg3^~tcd4~#testmsg4^~tcd1~#testmsg1^~tcd2~#testmsg2^~tcd3~#testmsg3^~tcd4~#testmsg4^~tcd1~#testmsg1^~tcd2~#testmsg2^~tcd3~#testmsg3^~tcd4~#testmsg4^~tcd1~#testmsg1^~tcd2~#testmsg2^~tcd3~#testmsg3^~tcd4~#testmsg4^~tcd1~#testmsg1^~tcd2~#testmsg2^~tcd3~#testmsg3^~tcd4~#testmsg4^~tcd1~#testmsg1^~tcd2~#testmsg2^~tcd3~#testmsg3^~tcd4~#testmsg4^~tcd1~#testmsg1^~tcd2~#testmsg2^~tcd3~#testmsg3^~tcd4~#testmsg4^~tcd1~#testmsg1^~tcd2~#testmsg2^~tcd3~#testmsg3^~tcd4~#testmsg4^~tcd1~#testmsg1^~tcd2~#testmsg2^~tcd3~#testmsg3^~tcd4~#testmsg4^~tcd1~#testmsg1^~tcd2~#testmsg2^~tcd3~#testmsg3^~tcd4~#testmsg4^~tcd1~#testmsg1^~tcd2~#testmsg2^~tcd3~#testmsg3^~tcd4~#testmsg4^~tcd1~#testmsg1^~tcd2~#testmsg2^~tcd3~#testmsg3^~tcd4~#testmsg4^~tcd1~#testmsg1^~tcd2~#testmsg2^~tcd3~#testmsg3^~tcd4~#testmsg4^~tcd1~#testmsg1^~tcd2~#testmsg2^~tcd3~#testmsg3^~tcd4~#testmsg4^~tcd1~#testmsg1^~tcd2~#testmsg2^~tcd3~#testmsg3^~tcd4~#testmsg4^~tcd1~#testmsg1^~tcd2~#testmsg2^~tcd3~#testmsg3^~tcd4~#testmsg4^~tcd1~#testmsg1^~tcd2~#testmsg2^~tcd3~#testmsg3^~tcd4~#testmsg4^~tcd1~#testmsg1^~tcd2~#testmsg2^~tcd3~#testmsg3^~tcd4~#testmsg4^~tcd1~#testmsg1^~tcd2~#testmsg2^~tcd3~#testmsg3^~tcd4~#testmsg4^~tcd1~#testmsg1^~tcd2~#testmsg2^~tcd3~#testmsg3^~tcd4~#testmsg4^~tcd1~#testmsg1^~tcd2~#testmsg2^~tcd3~#testmsg3^~tcd4~#testmsg4^~tcd1~#testmsg1^~tcd2~#testmsg2^~tcd3~#testmsg3^~tcd4~#testmsg4^~tcd1~#testmsg1^~tcd2~#testmsg2^~tcd3~#testmsg3^~tcd4~#testmsg4^~tcd1~#testmsg1^~tcd2~#testmsg2^~tcd3~#testmsg3^~tcd4~#testmsg4^~tcd1~#testmsg1^~tcd2~#testmsg2^~tcd3~#testmsg3^~tcd4~#testmsg4^~tcd1~#testmsg1^~tcd2~#testmsg2^~tcd3~#testmsg3^~tcd4~#testmsg4^~tcd1~#testmsg1^~tcd2~#testmsg2^~tcd3~#testmsg3^~tcd4~#testmsg4';
begin
delete from t;
insert into t
SELECT SUBSTR(str, start_pos, (next_pos-start_pos)) AS single_element, element_no
FROM (
SELECT
ilv.str,
nt.column_value AS element_no,
INSTR(ilv.str, '^~', DECODE(nt.column_value, 1, 0, 1), DECODE(nt.column_value, 1, 1, nt.column_value-1)) + 2 AS start_pos,
INSTR(ilv.str, '^~', 1, DECODE(nt.column_value, 1, 1, nt.column_value)) AS next_pos
FROM (
select '~' || myvar || '^~' as str,
(Length(myvar) - length(replace(myvar,'^~','')))/2 + 2 as no_of_elements
from dual) ilv,
TABLE(
CAST(
MULTISET(
SELECT ROWNUM FROM dual CONNECT BY ROWNUM < ilv.no_of_elements
) AS number_ntt )) nt
);
end;
The error I get when expanding "myvar" to 32000 characters is
将“myvar”扩展到 32000 个字符时出现的错误是
can bind a LONG value only for insert into a LONG column
Is there a way I can get around this size restraint because i'm not actually inserting this value into a table, i'm just using it in the query?
有没有办法绕过这个大小限制，因为我实际上并没有将此值插入表中，我只是在查询中使用它？
回答by Justin Cave
Do you have to define the variable as a VARCHAR2? Could you define it as a CLOB instead?
您是否必须将变量定义为 VARCHAR2？你能把它定义为 CLOB 吗？
If I change the declaration of MYVARfrom a VARCHAR2(32767)to a CLOBand define the NUMBER_NTTtype, your code runs for me
如果我将声明MYVAR从 a更改VARCHAR2(32767)为 aCLOB并定义NUMBER_NTT类型，您的代码将为我运行
SQL> ed
Wrote file afiedt.buf
SP2-0161: line 2 truncated.
1 declare
2 myvar clob := 'tcd1~#testmsg1^~tcd2~#testmsg2^~tcd3~#testmsg3^~tcd4~#testms
<<snip>>
~tcd3~#testmsg3^~tcd4~#testmsg4';
4 begin
5 delete from t;
6 insert into t
7 SELECT SUBSTR(str, start_pos, (next_pos-start_pos)) AS single_element, elem
ent_no
8 FROM (
9 SELECT
10 ilv.str,
11 nt.column_value AS element_no,
12 INSTR(ilv.str, '^~', DECODE(nt.column_value, 1, 0, 1), DECODE
(nt.column_value, 1, 1, nt.column_value-1)) + 2 AS start_pos,
13 INSTR(ilv.str, '^~', 1, DECODE(nt.column_value, 1, 1, nt.colu
mn_value)) AS next_pos
14 FROM (
15 select '~' || myvar || '^~' as str,
16 (Length(myvar) - length(replace(myvar,'^~','')))/2 + 2 as n
o_of_elements
17 from dual) ilv,
18 TABLE(
19 CAST(
20 MULTISET(
21 SELECT ROWNUM FROM dual CONNECT BY ROWNUM < ilv.n
o_of_elements
22 ) AS number_ntt )) nt
23 );
24* end;
25 /
PL/SQL procedure successfully completed.
SQL> select count(*) from t;
COUNT(*)
----------
172
That being said, that's not how I'd parse a delimited string, particularly in PL/SQL. But it does the job.
话虽如此，这不是我解析分隔字符串的方式，尤其是在 PL/SQL 中。但它可以完成工作。
回答by Michael Broughton
OK, well this skirts close to the edges of your implementation bias, although remember that forall IS a bulk-binding operation, not a real loop, but have you looked at the dbms_utility.comma_to_table function?
好的，这接近于你的实现偏差的边缘，虽然记住 forall 是一个批量绑定操作，不是一个真正的循环，但是你看过 dbms_utility.comma_to_table 函数吗？
It is an optimized internal oracle parsing function, although with some limitations as you can read about here: http://www.techiegyan.com/2009/02/17/oracle-breaking-comma-separated-string-using-dbms_utilitycomma_to_table/
它是一个优化的内部 oracle 解析函数，但有一些限制，你可以在这里阅读：http: //www.techiegyan.com/2009/02/17/oracle-break-comma-separated-string-using-dbms_utilitycomma_to_table/
You would need to replace() to make it comma-delimited, and also double-quotes-enclose if you have parsed fields that starts with numbers, special characters, contains commas, etc
如果您解析了以数字、特殊字符、包含逗号等开头的字段，则需要使用 replace() 使其以逗号分隔，并且还需要用双引号括起来
But if your data will allow - it sure makes your code look cleaner (and will likely work much faster too)
但是如果你的数据允许 - 它肯定会让你的代码看起来更干净（并且可能会更快地工作）
declare
myvar varchar2(32000) := 'tcd1~#testmsg1^~tcd2~#testmsg2^~tcd3~#testmsg3';
mycnt binary_integer;
myresults sys.dbms_utility.lname_array;
begin
sys.dbms_utility.comma_to_table('"'||replace(myvar,'^~','","')||'"', mycnt, myresults );
delete from t;
forall ix in myresults.first..myresults.last
insert into tvalues (myresults(ix));
commit;
end;
