OraQA

Oracle Question and Answer

  • Do you have a solution to a problem? Do you have an unanswered question? Login and share it with the Oracle community. More...

Oracle News


Entries RSS feed

Comments RSS feed

What is the max buffer size that can be set when calling DBMS_OUTPUT.ENABLE?

February 27th, 2006 By Eddie Awad

First, what does DBMS_OUTPUT.ENABLE do? This procedure enables calls to PUT, PUT_LINE, NEW_LINE, GET_LINE, and GET_LINES procedures in DBMS_OUTPUT. Calls to these procedures are ignored if the DBMS_OUTPUT is not ENABLEd. This procedure is mainly used in debugging PL/SQL programs.

Syntax

DBMS_OUTPUT.ENABLE (buffer_size IN INTEGER DEFAULT 20000);

Notes

  • It is not necessary to call DBMS_OUTPUT.ENABLE when you use the SERVEROUTPUT option of SQL*Plus.
  • You should generally avoid having application code invoke either the DBMS_OUTPUT.DISABLE Procedure or DBMS_OUTPUT.ENABLE Procedure because this could subvert the attempt of an external tool like SQL*Plus to control whether or not to display output.
  • If there are multiple calls to DBMS_OUTPUT.ENABLE, then buffer_size is the last of the values specified.
  • The default buffer size is 20,000 bytes.
  • The minimum buffer size is 2,000 bytes.

In 10gR1 (10.1) and before

The maximum buffer size is 1,000,000 bytes.

In 10gR2 (10.2) and above

  • The maximum buffer size is unlimited (when buffer_size = NULL).
  • When the user specifies buffer_size (NOT NULL), the maximum size is 1,000,000. Naturally, set buffer_size = NULL for unlimited size.
  • Typing SET SERVEROUTPUT ON in SQL*Plus has the effect of invoking DBMS_OUTPUT.ENABLE (buffer_size => NULL); with no limit on the output.

2 Responses to “What is the max buffer size that can be set when calling DBMS_OUTPUT.ENABLE?”

  1. tkyte Says:

    the versions should be:

    In 10gr1 and before….

    In 10gr2 and above….

    the unlimited was added in 10gr2…

  2. Eddie Awad Says:

    Thanks for the clarification. I have updated the post.

Leave a Reply

You must be logged in to post a comment.

RSS feed for comments on this question