ORU-10027：缓冲区溢出，限制为100000字节

发布于 2025-02-11 19:29:27 字数 826 浏览 3 评论 0原文

在PL/SQL中生成100K记录时，我的错误低于错误。我创建了一个软件包，并从匿名块中调用该软件包。

Error report -
ORA-20000: ORU-10027: buffer overflow, limit of 100000 bytes
ORA-06512: at "SYS.DBMS_OUTPUT", line 32
ORA-06512: at "SYS.DBMS_OUTPUT", line 97
ORA-06512: at "SYS.DBMS_OUTPUT", line 112
ORA-06512: at "APPS.PJM_ECC_DATA_POPULATION", line 126
ORA-06512: at line 13
20000. 00000 -  "%s"
*Cause:    The stored procedure 'raise_application_error'
           was called which causes this error to be generated.

我正在使用以下行打印日志，

dbms_output.put_line('After pjm_project_params_pkg.insert_row: Row ID: ' || l_rowid);

我已经阅读了一些答案，他们建议在下面使用。

DBMS_OUTPUT.ENABLE(1000000)

我不知道包裹在哪里我应该把它放在哪里？它会解决问题吗？我把下面放在匿名块中，但它无济于事

set serveroutput on size 1000000

原文

I am getting below error while generating 100k record in PL/SQL. I have created a package and calling that package from anonymous block.

Error report -
ORA-20000: ORU-10027: buffer overflow, limit of 100000 bytes
ORA-06512: at "SYS.DBMS_OUTPUT", line 32
ORA-06512: at "SYS.DBMS_OUTPUT", line 97
ORA-06512: at "SYS.DBMS_OUTPUT", line 112
ORA-06512: at "APPS.PJM_ECC_DATA_POPULATION", line 126
ORA-06512: at line 13
20000. 00000 -  "%s"
*Cause:    The stored procedure 'raise_application_error'
           was called which causes this error to be generated.

I am using below line to print log

dbms_output.put_line('After pjm_project_params_pkg.insert_row: Row ID: ' || l_rowid);

I have read some of the answers and they have suggested to use below.

DBMS_OUTPUT.ENABLE(1000000)

I dont know where in package I should put the same? will it solve the problem?
I put below in my anonymous block but it dit not help

set serveroutput on size 1000000

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

眸中客 2025-02-18 19:29:27

如果完全记录到服务器端文件，则UTL_FILE是最好的选择。它不会抱怨缓冲区溢出。

DECLARE 
  v_MyFileHandle UTL_FILE.FILE_TYPE;
  BEGIN

      --Change the folder based on host operating System
      
       v_MyFileHandle := UTL_FILE.FOPEN('C:\','LOG.TXT','a');

      FOR i in 1..1000000
      LOOP 
       UTL_FILE.PUT_LINE(v_MyFileHandle, ' Record written to file  at ' || TO_CHAR(SYSDATE,'MM-DD-YY HH:MI:SS AM')||' is '||i);
      END LOOP;
      
      UTL_FILE.FCLOSE(v_MyFileHandle);
       
 EXCEPTION
      WHEN OTHERS THEN
           DBMS_OUTPUT.PUT_LINE
                ('ERROR ' || TO_CHAR(SQLCODE) || SQLERRM);
 END;

您可以以仅读取模式打开文件，并且可以在脚本运行时编写记录时看到进度。这是一个奖励。

有关更多详细信息，值得阅读Oracle文档： https：//docs.oracle.com /Database/121/arpls/u_file.htm#arpls72681

If at all logging to a server side file is an option,then UTL_FILE is the best bet.It doesn't complain about buffer overflow.

DECLARE 
  v_MyFileHandle UTL_FILE.FILE_TYPE;
  BEGIN

      --Change the folder based on host operating System
      
       v_MyFileHandle := UTL_FILE.FOPEN('C:\','LOG.TXT','a');

      FOR i in 1..1000000
      LOOP 
       UTL_FILE.PUT_LINE(v_MyFileHandle, ' Record written to file  at ' || TO_CHAR(SYSDATE,'MM-DD-YY HH:MI:SS AM')||' is '||i);
      END LOOP;
      
      UTL_FILE.FCLOSE(v_MyFileHandle);
       
 EXCEPTION
      WHEN OTHERS THEN
           DBMS_OUTPUT.PUT_LINE
                ('ERROR ' || TO_CHAR(SQLCODE) || SQLERRM);
 END;

You can open the file in read-only mode and can see the progress as the records are written while the script is running.This is a bonus.

For more details worth reading oracle documentation : https://docs.oracle.com/database/121/ARPLS/u_file.htm#ARPLS72681

回复收藏 0 原文

手心的温暖 2025-02-18 19:29:27

如果您要吹DBMS_OUTPUT的限制，则可能应该使用记录表记录跟踪消息。

作为Oracle内置库，DBMS_OUTPUT具有可用性的优势。那是它的唯一优势。它的输出很难搜索，这是与输出大小的对数问题。这不是持久的。在其他环境中进行管理很麻烦。

不幸的是，Oracle不提供PL/SQL Logger实用程序，但您不必自己编写（除非您愿意）。使用泰勒·穆斯（Tyler Muth）的第三方图书馆。这是我们最接近行业标准的事情。在github上找到它。

回复收藏 0 原文

玻璃人 2025-02-18 19:29:27

我确实面临着同样的错误
ORA-20000：ORU-10027：缓冲区溢出，限制为100000字节

，因为我正在更新〜91K记录，并且由于一个条件错误
我的否则部分仅在我定义此错误代码

    V_CODE := SQLCODE;
    V_ERRM := SUBSTR(SQLERRM, 1, 100);
    DBMS_OUTPUT.PUT_LINE(' Error code ' || V_CODE || ': ' || V_ERRM );
    V_ERR_CNT := V_ERR_CNT + 1;

以解决此问题的位置执行，我将其如下所示

ALTER SESSION SET DB_FILE_MULTIBLOCK_READ_COUNT=128;
ALTER SESSION SET SORT_AREA_SIZE=500000000;
ALTER SESSION SET SORT_AREA_RETAINED_SIZE=500000000;
set serveroutput on size unlimited

，我还解决了结果集合中的IF条件部分，如结果集中，我只在打印计数。

i did face the same error
ORA-20000: ORU-10027: buffer overflow, limit of 100000 bytes

cause i am updating the ~91k records and due to one condition error
mine else part is only executing where i defined this error code

    V_CODE := SQLCODE;
    V_ERRM := SUBSTR(SQLERRM, 1, 100);
    DBMS_OUTPUT.PUT_LINE(' Error code ' || V_CODE || ': ' || V_ERRM );
    V_ERR_CNT := V_ERR_CNT + 1;

to resolve this issue , i put it as below

ALTER SESSION SET DB_FILE_MULTIBLOCK_READ_COUNT=128;
ALTER SESSION SET SORT_AREA_SIZE=500000000;
ALTER SESSION SET SORT_AREA_RETAINED_SIZE=500000000;
set serveroutput on size unlimited

and i also resolved the If condition part as in the result set i am only printing the count.

回复收藏 0 原文

~没有更多了~