ORA-20000: ORU-10027: buffer overflow, limit of 10000 bytes

PL/SQL Version 12c

This error occurred when I am trying to print the output of cursor to output window where output window default buffer size is 10000 bytes and cursor returning more than 20000 records.

DECLARE

  vCur      SYS_REFCURSOR;

  Column1   VARCHAR2(30);

BEGIN

  OPEN vCur FOR

    SELECT Column1 FROM TEST t;

    DBMS_OUTPUT.ENABLE(500000);

  LOOP

    FETCH vCur

      INTO Column1;

    EXIT WHEN vCur%NOTFOUND;

    dbms_output.put_line(Column1);

  END LOOP;

END;

Use following to increse buffer size in query statement.

-- Number is used to increse buffer size as specified number

DBMS_OUTPUT.ENABLE(500000);

-- NULL is used to increse buffer size unlimited

DBMS_OUTPUT.ENABLE(NULL);

Note:-

1. Depending on Oracle version, DBMS_OUTPUT has different default buffer size.

2. Its reduces the query performance, after analyzing output result comment DBMS_OUTPUT line in Store Procedure.