How to increase dbms output buffer

Question

I tried to debug my dynamic query via dbms output but seems like the query string is too long for dbms output buffer    I got    ORA-06502  PL SQL  numeric or value error  character string buffer too small ORA-06512  at  SYS DBMS OUTPUT   line 148 ORA-06512  at line 1    Any idea how to increase the buffer size

User · Answer

Here you go   DECLARE BEGIN   dbms output enable NULL   -- Disables the limit of DBMS   -- Your print here   END

User · Answer

When buffer size gets full  There are several options you can try   1  Increase the size of the DBMS OUTPUT buffer to 1 000 000  2  Try filtering the data written to the buffer - possibly there is a loop that writes to DBMS OUTPUT and you do not need this data   3  Call ENABLE at various checkpoints within your code  Each call will clear the buffer   DBMS OUTPUT ENABLE NULL  will default to 20000 for backwards compatibility  Oracle documentation on dbms output  You can also create your custom output display something  like below snippets  create or replace procedure cust output input string in varchar2   is      out string in long default in string      string lenth number      loop count number default 0    begin      str len    length out string in       while loop count  lt  str len    loop        dbms output put line  substr  out string in  loop count  1  255             loop count    loop count  255      end loop   end    Link -Ref  Alternative to dbms output putline   By  Alexander

User · Answer

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

[sql] How to increase dbms_output buffer?

Examples related to sql

Examples related to oracle

Examples related to plsql

Examples related to oracle10g

Examples related to dbms-output