Oracle-ORA-06502:PL/SQL:数字或值错误(DBMS_OUTPUT) [英] Oracle - ORA-06502: PL/SQL: numeric or value error (DBMS_OUTPUT)

问题描述

我实现了一个返回 clob 数据类型的函数，我想将结果打印在 DBMS输出中.不幸的是，我收到 ORA-06502:PL/SQL:数字或值错误，我认为这是由于DBMS_OUTPUT的大小所致.

I implemented a function that returns clob data-type, and I would like to print the result in DBMS Output. Unfortunately, I am getting ORA-06502: PL/SQL: numeric or value error and I think it is due to the size of DBMS_OUTPUT.

这是代码.

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;

以下内容可以帮助您了解此问题
1)添加了以下内容来设置缓冲区 unlimited 的大小，但是不起作用.

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..

DBMS_OUTPUT.ENABLE(NULL);

或

set serveroutput on size unlimited;

相关链接: http://www.oracle-developer.net/display .php?id = 327

2)注意，vr_output_str的大小为 75387 ，这就是为什么返回类型为 CLOB 的原因.

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.

DBMS_LOB.getlength(vr_output_str); // =75387

3)通过执行以下操作可以解决此问题，但是我认为这不是好的解决方案，因为它执行了3次该功能.

3) I can solve the issue by doing the following, but I think this is not a good solution since it executed the function three times.

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)我正在使用 Oracle数据库11g企业版11.2.0.3.0-64位生产版本

推荐答案

如果大于32767字节，则无法使用dbms_output.put_line直接打印Clob.

You will not be able to print a clob using dbms_output.put_line directly if it is greater than 32767 bytes.

在这种情况下，您可以创建一个过程来遍历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:

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;

请注意，v_chunk_size必须一次导致少于32767个字节被分块.如果编码每个字符有2个字节，则需要使用(32767/2).

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).

这篇关于Oracle-ORA-06502:PL/SQL:数字或值错误(DBMS_OUTPUT)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！