How to Increase Dbms_Output Buffer

How to increase dbms_output buffer?

You can Enable DBMS_OUTPUT and set the buffer size. The buffer size can be between 1 and 1,000,000.

dbms_output.enable(buffer_size IN INTEGER DEFAULT 20000);
exec dbms_output.enable(1000000);

Check this

EDIT

As per the comment posted by Frank and Mat, you can also enable it with Null

exec dbms_output.enable(NULL);

buffer_size : Upper limit, in bytes, the amount of buffered information. Setting buffer_size to NULL specifies that there should be no limit. The maximum size is 1,000,000, and the minimum is 2,000 when the user specifies buffer_size (NOT NULL).

dbms_output size buffer overflow

In the procedure service_update, by any chance is there a call to

dbms_output.enable(30000); 

This may override the first limit you set.

How do I SPOOL output without reaching buffer limit when setting TERMOUT OFF doesnt work

From the SQL*Plus documentation, which largely applies to SQL Developer too:

TERMOUT OFF does not affect output from commands you enter interactively

If you had set termout off in a script file and ran it with @<script> then it would suppress out the output; but it would still be trying to to write the same data if you were spooling.

If you're seeing ORU-10027: buffer overflow then you're trying to solve the wrong problem anyway; even spooling, you'd hit the same error - it would just appear in the file and not in the script output window.

The buffer relates to dbms_output itself:

set serveroutput on size 2000

begin
  dbms_output.put_line(dbms_random.string('a', 2001));
end;
/

ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes
ORA-06512: at "SYS.DBMS_OUTPUT", line 32
...

To avoid that error set the buffer high enough for the amount of output you expect, or to its maximum for the Oracle and client version:

set serveroutput on size unlimited

but as you've seen that still caps you to 1000000 bytes in SQL Developer, up to version 18.2 anyway.

Even with termout off and spooling to a file, you'll still get

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

reported in the file.

If you need more output than that then you could write the output to a temporary table and then query it after your PL/SQL block, perhaps. Or you could use utl_file to write to a file (which has to be on the server, not the client, which is a downside) instead of spooling. Or you could use SQL*Plus, which doesn't impose that limitation.

Selecting the dbms_output buffer from a PL/SQL query?

Here is an example of the function you will not modify. I assume it puts only one line.

create or replace function iscustomereligible return boolean is
begin
   dbms_output.enable(1000000);
   dbms_output.put_line('hi');
   return true;
end;
/

Here is a function that will concatenate the results of the function you will not modify with the dbms_output line apparently written by that function.

create or replace function debug_iscustomereligible return varchar2 is
   v_line varchar2(4000);
   v_status number;
   v_el     boolean;
begin
   v_el := iscustomereligible;
   dbms_output.get_line(v_line, v_status); -- ignoring v_status if null is ok
   return 'customer ' || case v_el when true then 'eligible' else 'ineligible' end || ': ' || v_line;
end;
/

And here is how you would query the results if you were forced to do so only by executing a query.

select debug_iscustomereligible() from dual;

ORU-10027: buffer overflow, limit of 100000 bytes

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.

buffer overflow, limit of 1000000 bytes in oracle database

As @David pointed out, you don't need necessarily need to do this with a cursor loop or with PL/SQL at all; but at least part of the problem with your code is that you are looping forever.

Your loop is essentially:

OPEN cr;
LOOP
    -- do stuff
    EXIT WHEN cr%notfound;
END LOOP;
CLOSE cr;

You are not ever fetching data from the cursor. As it says in the documentation:

named_cursor%NOTFOUND has one of these values:

• If cursor is not open, INVALID_CURSOR.
• If cursor is open but no fetch was tried, NULL.
• If the most recent fetch returned a row, FALSE.
• If the most recent fetch did not return a row, TRUE.

You have opened the cursor, but haven't fetched, so the second bullet applies and cr%notfound is null - which is not true (or false), so the exit doesn't happen.

So you loop forever; or in your actual code, until you hit the buffer limit.

You need to add a fetch, and should also test for %notfound immediately rather than after your updates, otherwise the last found values will be applied twice (which might not really matter here, but usually will).

OPEN cr;
LOOP
    FETCH cr INTO c_id,c_value, c_comm_id;
    EXIT WHEN cr%notfound;
    -- do stuff
END LOOP;

So your code becomes:

BEGIN
    OPEN cr;
    LOOP
        FETCH cr INTO c_id,c_value, c_comm_id;
        EXIT WHEN cr%notfound;
        dbms_output.put_line(c_id || ' ' || c_value || '' || c_comm_id);

        update t_person_login set username ='anonymous@gmail.com' where person_id =c_id;
        update t_person_login_history set username ='anonymous@gmail.com' where person_id =c_id;
        update t_person set first_name ='anonymous',last_name ='anonymous' where id = c_id;
        update t_person_communication_method set value ='anonymous@gmail.com' where type = 'EMAIL' and person_id =c_id;
        update t_person_communication_method set value ='00000000000' where type = 'PHONE' and person_id =c_id and id=c_comm_id;
        update t_person_comm_method_history set value = 'anonymous@gmail.com' where type ='EMAIL' and person_id =c_id;
        update t_person_address set postcode ='anonymous',address_1 ='anonymous',city='anonymous',county ='anonymous' where person_id =c_id;        
    END LOOP;

    CLOSE cr;
END;

---

Related Topics