Tag Archives: ORA-20000: ORU-10027: buffer overflow

When debugging stored procedure, ora-20000: oru-10027: buffer overflow is prompted

In the afternoon, I debugged a stored procedure on PL/SQL developer 10.0.5.1710, and used more DBMS during debugging_ OUTPUT.PUT_ Line is a way to print logs. As a result, it was not long before the PLSQL client started to prompt the following errors: ora-20000: oru-10027: buffer overflow, limit of 10000 bytes. After analysis, it was because of the DBMS function_ OUTPUT.PUT_ Line () prints the result in the loop body. Therefore, this function will be executed as many times as the loop is executed, resulting in the buffer being full soon. The specific error information is shown in the figure below

So Google decided to use the keywords “ora-20000: oru-10027: buffer overflow, limit of 10000 bytes”. The first two of the query results are the solutions from the Oracle forum. The two links are https://community.oracle.com/message/977489 And https://community.oracle.com/thread/308557 The solution is also very simple, which is to add the following content at the beginning of the method body of our stored procedure: DBMS_ OUTPUT.ENABLE(1000000); make the output buffer capacity reach 10 million bytes, so that its capacity will not burst. The specific reference codes are as follows:

CREATE OR REPLACE PROCEDURE SAMPLE_PROC
IS
--Definition of various temporary variables and cursors
	CURSOR CURSOR_SAMPLE IS SELECT ... FROM TABLE_NAME WHERE ... ;
BEGIN
	-- method body starts here
	DBMS_OUTPUT.ENABLE(1000000);
	FOR I IN CURSOR_SAMPLE LOOP
		--Example of output statement
		DBMS_OUTPUT.PUT_LINE(...) ;
	END LOOP;
	--other content
END;
/

If you are writing stored procedures from the command line, you can use statements directly

SET SERVEROUTPUT ON size ‘1000000’; to set the maximum buffer size.

Of course, if the log capacity you need to output is really large, it is recommended not to use DBMS_ Output, but use UTL_ File output to the specified log file, a detailed reference example is shown in

http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/u_File. HTM#arpls70903, which is based on UNIX system, but can also be easily moved to Windows platform.