oracle 在 BLOB 上使用 DBMS_LOB.SUBSTR 导致 ORA-06502
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7932488/
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 DBMS_LOB.SUBSTR on a BLOB results in ORA-06502
提问by Zach Green
When I try to run the dbms_lob.substr function on a BLOB field, I get the following error:
当我尝试在 BLOB 字段上运行 dbms_lob.substr 函数时，出现以下错误：
ORA-06502: PL/SQL: numeric or value error: raw variable length too long
ORA-06512: at line 1
ORA-06502：PL/SQL：数字或值错误：原始变量长度太长
ORA-06512：在第 1 行
My query:
我的查询：
select dbms_lob.substr(my_report, 10000, 1)
from my_table where my_table.report_id = :myid
According to the dbms_lob.substrdocumentation, I should be able to use a value in the 2nd parameter up to 32767, and the size of the report is over 200,000 bytes, so it is within the range.
根据dbms_lob.substr文档，我应该可以使用第二个参数中的值最大为32767，并且报告的大小超过200,000字节，因此在范围内。
After playing with the number, I have found that the make value that I can use in the amount parameter (2nd parameter) to the substr function is 2000.
玩过数字后，我发现我可以在 substr 函数的数量参数（第二个参数）中使用的 make 值是 2000。
Does anyone know why?
有谁知道为什么？
回答by Zach Green
The function is returning the result as the RAW datatype, and the RAW datatype has a maximum size of 2000 bytes.
该函数将结果作为 RAW 数据类型返回，RAW 数据类型的最大大小为 2000 字节。
References:
参考：
http://download.oracle.com/docs/cd/B10501_01/server.920/a96540/sql_elements2a.htm#SQLRF0021
http://download.oracle.com/docs/cd/B10501_01/server.920/a96540/sql_elements2a.htm#SQLRF0021
回答by collapsar
The length limitation of 2000 octets applies to the sql engine only. In Pl/sql you may exploit the whole range of up to a length of 32767 (2^15-1).
2000 个八位字节的长度限制仅适用于 sql 引擎。在 Pl/sql 中，您可以利用长达 32767 (2^15-1) 的整个范围。
As of 12c, the length limitation of 2000 has been lifted.
从 12c 开始，2000 的长度限制已经取消。
However, prior to 12c there is a length limitation in the sqlplus client that does not allow for column sizes above 4000 ( The value for 11g2 ).
但是，在 12c 之前，sqlplus 客户端存在长度限制，不允许列大小超过 4000（11g2 的值）。
The following code works for 11g2 and later
以下代码适用于 11g2 及更高版本
var myid number;
exec :myid := 1234; -- whatever
DECLARE
l_r RAW(32767);
BEGIN
select dbms_lob.substr ( my_report, 2000, 1 ) head
into l_r
from my_table
where my_table.report_id = :myid
;
l_r := UTL_RAW.COPIES ( l_r, 10 );
dbms_output.put_line ( 'id ' || :myid || ', len(l_r) = ' || utl_raw.length(l_r));
END;
/
show errors
... while this version requires 12c:
...虽然此版本需要 12c：
var myid number;
exec :myid := 1234; -- whatever
DECLARE
l_r RAW(32767);
BEGIN
select dbms_lob.substr ( my_report, 32767, 1 ) head
into l_r
from my_table
where my_table.report_id = :myid
;
dbms_output.put_line ( 'id ' || :myid || ', len(l_r) = ' || utl_raw.length(l_r));
END;
/
show errors
