Search code examples
sqldatabaseoracle-databaseplsqloracle11g

ORU-10027: buffer overflow, limit of 100000 bytes

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
Solution

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