DBMS_OUTPUT.PUT_LINE not printing - Why do we need set serveroutput on" to see output on console

It is very common problem faced by people trying to execute PL/SQL procedure in SQL Plus or SQL Developer and displaying message or debugging information using DBMS_OUTPUT package. However, no output on console. Why DBMS_OUTPUT.PUT_LINE is not working ? 
DBMS_OUTPUT package enables us to send messages from stored procedures(DBMS server) to client. However, most tools (like SQL*PLUS, SQL developer) do not allocate a buffer for DBMS_OUTPUT to write in this buffer and do not attempt to read from that buffer after PL/SQl program execution.
Fortunately, we have commands (set serveroutput on size <Size of buffer>) to hint DBMS server to dump/flush server output to the client's buffer and client will read from this buffer to display output accordingly.
Before, Oracle 10.2 , there was a upper limit on size of this buffer (limited to one million bytes with a default size of 20,000). However, from oracle 10.2 the upper limit has been removed with restriction on size of buffer, if you specify.We have two cases here:- Specify size of buffer & do not specify size.
  1. If we specify size of buffer:-  Buffer size should be between 2000 and 1 million, else it will throw error.See following execution of set command
        SQL> set serveroutput on size 1000;
        SP2-0547: size option 1000 out of range (2000 through 1000000) 
        SQL> set serveroutput on size 3459988000;
        SP2-0547: size option 3459988000 out of range (2000 through 1000000)
        SQL> set serveroutput on size 30000;   --No error - size in range

  2. If we do not specify size of buffer :- From oracle 10.2 , if you do not specify size of buffer, implicitly buffer size will be set to NULL. NULL means unlimited size of buffer;
    SQL> set serveroutput on    --No error - default size set to NULL

Let's write s small PL/SQL program and see the difference , when server output is on/off. Follow the following diagram: First run: server output OFF, second run - server output ON
In first execution , server output is off so no output message displayed on console. Before second run, we set serveroutput on (as highlighted in above diagram) and this time output message displayed on console.
How do we enable DBMS server output in SQL Developer ?
In SQL Developer also , by default server output is not visible and we need to enable it . Follow the path :  Go to View (menu- bar) -> Open DBMS output .
Now click on , green + button and select the connection from drop down. It will switch on serveroutput.


 ====== End of the article======

5 Comments

  1. Thanks helpful.

    ReplyDelete
  2. Businesses are losing an estimated 17,000 Euros a year, amounting to around 663 billion Euros across Europe as a whole; all this mainly on account of poor or inadequate print management. Below, we offer-up three considered suggestions as to how any organisation can go about improving its print management and reducing its printing costs. singapore asia printing

    ReplyDelete
  3. Thanks for sharing such an amazing piece of content. A well-designed website with easy navigation enhances the user experience, encouraging visitors to explore further and potentially convert into leads. Integration of search filters and interactive maps simplifies property searches, making it convenient for potential buyers to find their ideal homes. Moreover, website development services can incorporate lead capture forms, enabling real estate professionals to collect valuable information from interested clients. Mobile responsiveness ensures accessibility from various devices, catering to the modern, on-the-go audience. Ultimately, a well-crafted website helps real estate businesses establish a strong online presence, attract a wider audience, and increase their chances of turning prospects into satisfied customers.

    ReplyDelete
  4. Your blog is a gem in the sea of online content. Thanks for consistently delivering valuable insights.
    Fmovie

    ReplyDelete
Previous Post Next Post