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