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 10000003 Answers
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 :
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.
0i 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.
