<small id='sVfDO'></small><noframes id='sVfDO'>

    • <bdo id='sVfDO'></bdo><ul id='sVfDO'></ul>
    <tfoot id='sVfDO'></tfoot>
    <i id='sVfDO'><tr id='sVfDO'><dt id='sVfDO'><q id='sVfDO'><span id='sVfDO'><b id='sVfDO'><form id='sVfDO'><ins id='sVfDO'></ins><ul id='sVfDO'></ul><sub id='sVfDO'></sub></form><legend id='sVfDO'></legend><bdo id='sVfDO'><pre id='sVfDO'><center id='sVfDO'></center></pre></bdo></b><th id='sVfDO'></th></span></q></dt></tr></i><div id='sVfDO'><tfoot id='sVfDO'></tfoot><dl id='sVfDO'><fieldset id='sVfDO'></fieldset></dl></div>

    <legend id='sVfDO'><style id='sVfDO'><dir id='sVfDO'><q id='sVfDO'></q></dir></style></legend>
      1. Oracle - ORA-06502: PL/SQL: 数字或值错误 (DBMS_OUTPUT)

        Oracle - ORA-06502: PL/SQL: numeric or value error (DBMS_OUTPUT)(Oracle - ORA-06502: PL/SQL: 数字或值错误 (DBMS_OUTPUT))
            <i id='r7LG7'><tr id='r7LG7'><dt id='r7LG7'><q id='r7LG7'><span id='r7LG7'><b id='r7LG7'><form id='r7LG7'><ins id='r7LG7'></ins><ul id='r7LG7'></ul><sub id='r7LG7'></sub></form><legend id='r7LG7'></legend><bdo id='r7LG7'><pre id='r7LG7'><center id='r7LG7'></center></pre></bdo></b><th id='r7LG7'></th></span></q></dt></tr></i><div id='r7LG7'><tfoot id='r7LG7'></tfoot><dl id='r7LG7'><fieldset id='r7LG7'></fieldset></dl></div>
            • <bdo id='r7LG7'></bdo><ul id='r7LG7'></ul>

                <tbody id='r7LG7'></tbody>
            • <small id='r7LG7'></small><noframes id='r7LG7'>

                <legend id='r7LG7'><style id='r7LG7'><dir id='r7LG7'><q id='r7LG7'></q></dir></style></legend>
                <tfoot id='r7LG7'></tfoot>

                  本文介绍了Oracle - ORA-06502: PL/SQL: 数字或值错误 (DBMS_OUTPUT)的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着跟版网的小编来一起学习吧!

                  问题描述

                  我实现了一个返回clob数据类型的函数,我想在DBMS输出中打印结果.不幸的是,我收到 ORA-06502: PL/SQL: numeric or value error,我认为这是由于 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 Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

                  推荐答案

                  如果大于 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)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!

                  本站部分内容来源互联网,如果有图片或者内容侵犯了您的权益,请联系我们,我们会在确认后第一时间进行删除!

                  相关文档推荐

                  How to redirect the output of DBMS_OUTPUT.PUT_LINE to a file?(如何将 DBMS_OUTPUT.PUT_LINE 的输出重定向到文件?)
                  How do I get column datatype in Oracle with PL-SQL with low privileges?(如何使用低权限的 PL-SQL 在 Oracle 中获取列数据类型?)
                  Get a list of all functions and procedures in an Oracle database(获取 Oracle 数据库中所有函数和过程的列表)
                  Why cannot I create triggers on objects owned by SYS?(为什么我不能在 SYS 拥有的对象上创建触发器?)
                  Returning result even for elements in IN list that don#39;t exist in table(即使对于表中不存在的 IN 列表中的元素也返回结果)
                  Reset Sequence in oracle 11g(oracle 11g 中的重置序列)
                1. <tfoot id='H5Pfq'></tfoot>

                  <legend id='H5Pfq'><style id='H5Pfq'><dir id='H5Pfq'><q id='H5Pfq'></q></dir></style></legend>

                  <small id='H5Pfq'></small><noframes id='H5Pfq'>

                      <i id='H5Pfq'><tr id='H5Pfq'><dt id='H5Pfq'><q id='H5Pfq'><span id='H5Pfq'><b id='H5Pfq'><form id='H5Pfq'><ins id='H5Pfq'></ins><ul id='H5Pfq'></ul><sub id='H5Pfq'></sub></form><legend id='H5Pfq'></legend><bdo id='H5Pfq'><pre id='H5Pfq'><center id='H5Pfq'></center></pre></bdo></b><th id='H5Pfq'></th></span></q></dt></tr></i><div id='H5Pfq'><tfoot id='H5Pfq'></tfoot><dl id='H5Pfq'><fieldset id='H5Pfq'></fieldset></dl></div>
                        <tbody id='H5Pfq'></tbody>
                            <bdo id='H5Pfq'></bdo><ul id='H5Pfq'></ul>