Sep 13, 2014

Textual description of firstImageUrl

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======

Location: Hyderabad, Telangana, India