SET SERVEROUTPUT ON in PL/SQL: Why It Matters and How to Use It
When working with Oracle PL/SQL, one of the first and most essential commands you'll encounter is:
SET SERVEROUTPUT ON;
Though it looks simple, this command plays a crucial role in how PL/SQL programs behave, especially when you want to display output from procedures, anonymous blocks, or scripts using DBMS_OUTPUT.PUT_LINE.
Why Do We Use SET SERVEROUTPUT ON?
By default, PL/SQL code runs silently inside the Oracle database engine. This means that even if your program generates output, you won’t see any result on your screen — unless you explicitly enable server output.
The command SET SERVEROUTPUT ON instructs SQL*Plus or SQL Developer to display output from the DBMS_OUTPUT buffer, allowing you to see the results of your PL/SQL program.
Syntax
SET SERVEROUTPUT ON;
You can also control the buffer size and format (optional):
SET SERVEROUTPUT ON SIZE 1000000 FORMAT WRAPPED;
When Should You Use It?
Use SET SERVEROUTPUT ON in situations like:
When using DBMS_OUTPUT.PUT_LINE to display output.
During testing and debugging of PL/SQL procedures or anonymous blocks.
To trace variable values or track the flow of control in your code.
Example: Using SET SERVEROUTPUT ON in a PL/SQL Block
Here’s a simple example that declares variables and uses DBMS_OUTPUT.PUT_LINE to display them:
SET SERVEROUTPUT ON;
DECLARE
eno NUMBER(5) NOT NULL := 2;
ename VARCHAR2(15) := 'Branson Devs';
edept CONSTANT VARCHAR2(15) := 'Web Developer';
BEGIN
dbms_output.put_line('Declared Values:');
dbms_output.put_line(' Employee Number: ' || eno || ' Employee Name: ' || ename);
dbms_output.put_line('Constant Declared:');
dbms_output.put_line(' Employee Department: ' || edept);
END;
/
Output (Only Visible If SERVEROUTPUT Is ON):
Declared Values:
Employee Number: 2 Employee Name: Branson Devs
Constant Declared:
Employee Department: Web Developer
Important: If you omit SET SERVEROUTPUT ON, the DBMS_OUTPUT.PUT_LINE results will not be displayed, even though the block executes successfully.
Tips for Using SET SERVEROUTPUT ON
In SQL Developer, go to View > DBMS Output, then click the green + icon to enable output for your session.
In SQL*Plus, always run SET SERVEROUTPUT ON before any PL/SQL block that uses output.
Use SET SERVEROUTPUT OFF when you no longer need the output to be displayed.
Conclusion
The SET SERVEROUTPUT ON command is small but vital for writing and debugging PL/SQL code. It provides visibility into your PL/SQL logic by allowing output messages to be displayed on screen, making your development workflow smoother and more transparent.