ORU-10027: buffer overflow, limit of 100000 bytes

Question

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

Wallflower

you can't put "set server output on" in a package. It has to be set in the client application, in the sqlplus session – Brainbrainard

DBMS_OUTPUT.ENABLE() would go in your code block after the BEGIN and before any call to DBMS_OUTPUT.PUT_LINE. But your real issue is trying to output too much text. I would only use that within the small portion of code you want to trace through. Another option would be to INSERT into a logging table. – Oberstone

If you are in Oracle 10.2 and higher than you can use "set serveroutput on size unlimited" or "set serveroutput on ", this should not be done in Production as it will give you ORA-0403 if you exceed the memory. The other option is to substring and display a smaller string. – Milissamilissent

Answer 1

If you're blowing the limits of DBMS_OUTPUT you should probably use a logging table to record your trace messages.

Being an Oracle built-in library, DBMS_OUTPUT has the advantage of availability. That is its only advantage. Its output is hard to search, a problem which is logarithmic to the size of output. It is not persistent. It is troublesome to manage in other environments.

Unfortunately Oracle does not provide a PL/SQL logger utility but you don't have to write your own (unless you want to). Use Tyler Muth's third-party library. It is the closest thing we have to an industry standard. Find it on GitHub.

Damn

Answer 2

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

Increase

Answer 3

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.

Cronyism