Maximum buffer size for DBMS_OUTPUT.ENABLE

Asked By Francine Piper 10 points N/A Posted on - 04/11/2012

When using the DBMS_OUTPUT.ENABLE, what is the maximum buffer size that can be specified by it?

• Status: Open
• Question Views: 4379
• Answer Count: 2
• Vote Up 0 Vote Down

• Answer Accepted: Yes
• Question Category: Oracle Database

Best Answer by Sumabat hath

Go To Solution

Best Answer

Best Answer

Answered By Sumabat hath 0 points N/A #115350

Maximum buffer size for DBMS_OUTPUT.ENABLE

The maximum size of The DBMS_OUTPUT.ENABLE function is 1,000,000  and the minimum size is 2,000 up to oracle 10g versions when the user specifies buffer size (NOTNULL).And Buffer_size IN INTEGER DEFAULT 2000The syntax is DBMS_OUTPUT.ENABLE (the default is 20,000 for backwards compatibility with earlier database versions that did not support unlimited buffering. Now oracle 10g release 2 buffer size is unlimited. The DBMS_OUTPUT.ENABLE enables calls to various functions PUT, PUT_LINE, NEW_LINE, GET_LINE, and GET_LINES and DBMS_OUTPUT Package needs to be activated for calls to procedures. The buffer size is   NULL where string buffer limit is not specified.

About Sumabat hath

Questions
1

Answers
33

Best Answers
3

• Vote Up 0 Vote Down

• Posted on - 04/18/2012
• Question Category: Oracle Database

Answered By Carl Jack 10 points N/A #115351

Maximum buffer size for DBMS_OUTPUT.ENABLE

Hello Francine,

It will depend on the version of Oracle that you're using. In case you  are using Oracle 10g (10.1) and before, then you will use a maximum buffer size of 1,000,000 bytes.

But if you are using Oracle 10gR2 (10.2) and above, then you have no limit to the buffer size:

• The maximum buffer size that you can use is unlimited (when buffer_size = NULL).
• In the event that a user specifies buffer_size (NOT NULL), then the maximum size will be 1,000,000. Naturally, you will need to set buffer_size = NULL for unlimited size.
• If you type SET SERVEROUTPUT ON in SQL*Plus, it will have the effect of invoking DBMS_OUTPUT.ENABLE (buffer_size => NULL); with no limit on the output.

Regards,

Carl

 

About Carl Jack

Questions
1

Answers
1657

Best Answers
417

• Vote Up 0 Vote Down

• Posted on - 09/09/2012
• Question Category: Oracle Database