Oracle - ORA-06502: PL/SQL: 数字或值错误 (DBMS_OUTPUT)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26723362/
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 - ORA-06502: PL/SQL: numeric or value error (DBMS_OUTPUT)
提问by Adrian
I implemented a function that returns clobdata-type, and I would like to print the result in DBMS Output. Unfortunately, I am getting ORA-06502: PL/SQL: numeric or value errorand I think it is due to the size of DBMS_OUTPUT.
我实现了一个返回clob数据类型的函数，我想在DBMS Output 中打印结果。不幸的是，我收到ORA-06502: PL/SQL: numeric or value error，我认为这是由于 DBMS_OUTPUT 的大小。
This is the code.
这是代码。
DECLARE
TYPE tp_col_array IS TABLE OF varchar2(32767);
FUNCTION my_fn (
p_in_proc_date IN varchar2)
RETURN clob AS
vr_output_str clob;
BEGIN
-- Detailed code hidden due to privacy. Sorry
RETURN vr_output_str;
EXCEPTION
WHEN LOGIN_DENIED
THEN
DBMS_OUTPUT.PUT_LINE('Invalid username/password: logon denied');
RETURN 'TEST Terminated';
END my_fn;
BEGIN
DBMS_OUTPUT.PUT_LINE(my_fn('31-AUG-14'));
END;
Here are something that can help you to understand this issue
1) Added the following to set the size of buffer unlimited, but did not work..
这里有一些可以帮助您理解此问题的内容
1) 添加了以下内容以设置缓冲区的大小不受限制，但没有用。
DBMS_OUTPUT.ENABLE(NULL);
or
或者
set serveroutput on size unlimited;
Related link: http://www.oracle-developer.net/display.php?id=327
2) It is noted that the size of vr_output_str is 75387, and that is why the return type is CLOB.
相关链接：http://www.oracle-developer.net/display.php?id=327
2）值得注意的是，vr_output_str的大小是75387，这就是为什么返回类型为CLOB。
DBMS_LOB.getlength(vr_output_str); // =75387
3) I can solve the issue by doing the following, but I think this is not a good solutionsince it executed the function three times.
3）我可以通过执行以下操作来解决问题，但我认为这不是一个好的解决方案，因为它执行了该函数3次。
DBMS_OUTPUT.PUT_LINE(SUBSTR(my_fn ('31-AUG-14'), 1, 32767));
DBMS_OUTPUT.PUT_LINE(SUBSTR(my_fn ('31-AUG-14'), 32768, 32767));
DBMS_OUTPUT.PUT_LINE(SUBSTR(my_fn ('31-AUG-14'), 65536, 32767));
4) I am using Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
4) 我使用的是Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
回答by Dave Lyndon
You will not be able to print a clob using dbms_output.put_line directly if it is greater than 32767 bytes.
如果大于 32767 字节，您将无法直接使用 dbms_output.put_line 打印 clob。
If this is the case you can create a procedure to iterate through the clob and print out one smaller chunk at a time. Such a procedure and test script is below:
如果是这种情况，您可以创建一个过程来遍历 clob 并一次打印出一个较小的块。这样的程序和测试脚本如下：
declare
c clob;
procedure print_clob( p_clob in clob ) is
v_offset number default 1;
v_chunk_size number := 10000;
begin
loop
exit when v_offset > dbms_lob.getlength(p_clob);
dbms_output.put_line( dbms_lob.substr( p_clob, v_chunk_size, v_offset ) );
v_offset := v_offset + v_chunk_size;
end loop;
end print_clob;
begin
for i in 1..10000 loop
c := c || 'test';
end loop;
--This will result in ora-06502
--dbms_output.put_line(c);
print_clob(c);
end;
Note that v_chunk_size must result in less than 32767 bytes being chunked at-a-time. If you encoding has 2 bytes per char you will need to use (32767/2).
请注意， v_chunk_size 必须导致一次分块少于 32767 个字节。如果编码每个字符有 2 个字节，则需要使用 (32767/2)。
回答by xor
The following procedure will better:
下面的程序会更好：
- Oracle 10g has limitation on put_line (a maximum of 32767 characters), But Oracle before 10g has a maximum of 255 characters limitation.
- the 'put_line' adding end of line on every iteration loop during output clob. So we use put() better (and 'DBMS_OUTPUT.NEW_LINE' at end).
- Oracle 10g 对 put_line 有限制（最多 32767 个字符），但 Oracle 10g 之前的最多 255 个字符限制。
- 'put_line' 在输出 clob 期间在每个迭代循环上添加行尾。所以我们更好地使用 put()（最后使用 'DBMS_OUTPUT.NEW_LINE'）。
PROCEDURE print_clob( p_clob in clob ) IS
v_offset number default 1;
v_chunk_size number := 255;
BEGIN
LOOP
EXIT when v_offset > dbms_lob.getlength(p_clob);
dbms_output.put( dbms_lob.substr( p_clob, v_chunk_size, v_offset ) );
v_offset := v_offset + v_chunk_size;
END LOOP;
DBMS_OUTPUT.NEW_LINE;
END print_clob;
