Comprehensive Analysis of SET SERVEROUTPUT ON Usage and DBMS_OUTPUT Mechanism in Oracle

Introduction

In Oracle database development, many developers frequently encounter output display issues when writing stored procedures. A common mistake is directly using the SET SERVEROUTPUT ON command within PL/SQL code, which results in compilation errors. This article explains the underlying reasons for this phenomenon and provides correct solutions.

Error Case Analysis

Let's first analyze a typical erroneous implementation. A developer creates a discount calculation procedure attempting to output price information for DVD and Blu-ray movies:

create or replace PROCEDURE discount  
is --- signature 
BEGIN --- executable part
update dvd set me_our_price = me_our_price*0.90 WHERE me_release_year = 2011;
update dvd set me_our_price = me_our_price*0.80 WHERE me_release_year = 2010;
update bluray set me_our_price = me_our_price*0.95 WHERE me_release_year = 2011;
update bluray set me_our_price = me_our_price*0.90 WHERE me_release_year = 2010;
DBMS_OUTPUT.PUT_LINE(' Blurays '); 
for i in ( 
SELECT e.mo_title, e.mo_bluray.me_list_price as me_list_price, e.mo_bluray.me_our_price    as  me_our_price FROM movie e  where e.mo_bluray is not null
 ) 
loop 
DBMS_OUTPUT.PUT_LINE(i.mo_title|| '  ' || i.me_list_price|| '  ' || i.me_list_price); 
end loop; 
DBMS_OUTPUT.PUT_LINE(' DVDs '); 
for i in ( 
set serveroutput on
SELECT e.mo_title, e.mo_dvd.me_list_price as me_list_price, e.mo_dvd.me_our_price as      me_our_price FROM movie e  where e.mo_dvd is not null
 ) 
loop 
DBMS_OUTPUT.PUT_LINE(i.mo_title|| '  ' || i.me_list_price|| '  ' || i.me_list_price); 
end loop; 
END discount;

This code produces a compilation error: PLS-00103: Encountered the symbol "SERVEROUTPUT". The error occurs because SET SERVEROUTPUT ON is a SQL*Plus environment setting command, not a valid PL/SQL language statement.

Deep Analysis of DBMS_OUTPUT Working Mechanism

To understand why this error occurs, we need to deeply examine the working mechanism of Oracle's DBMS_OUTPUT package.

Output Buffer Concept

When we call DBMS_OUTPUT.PUT_LINE in PL/SQL code, the output content is not immediately displayed on the screen. Instead, this information is stored in a special buffer located in the SGA (Shared Global Area) memory region. The buffer size defaults to approximately 2000 bytes but can be adjusted through configuration.

It's important to understand that this buffer is only created when using the DBMS_OUTPUT package. This means that without calling relevant output procedures, no corresponding buffer allocation occurs.

Necessity of Environment Variable Setting

The SET SERVEROUTPUT ON command tells the PL/SQL engine: "Please print the parameter values I pass in DBMS_OUTPUT.PUT_LINE to the main console." This setting is session-level and needs to be set only once at the beginning of each session.

From a technical implementation perspective, when executing SET SERVEROUTPUT ON:

• The system calls the DBMS_OUTPUT.ENABLE procedure, setting the default buffer size to 20000 bytes
• Sets an internal flag in the command line processor (CLP) or CLPPlus
• When this flag is enabled, the application calls the GET_LINES procedure after executing each SELECT or CALL statement
• Messages are redirected from the message buffer to standard output

Correct Usage Methods

Proper Configuration in SQL*Plus

In the SQL*Plus environment, the correct approach is to set server output at the beginning of the session:

SET SERVEROUTPUT ON
-- Then execute the stored procedure
EXEC discount;

Alternatively, place the setting command before calling the procedure:

SET SERVEROUTPUT ON SIZE 1000000
BEGIN
    discount;
END;
/

Configuration in SQL Developer

For Oracle SQL Developer users, the configuration method differs:

1. Select View → DBMS Output from the menu
2. Click the green plus icon
3. Select the appropriate schema
4. Execute the stored procedure

Output can also be explicitly enabled within the procedure:

BEGIN
    dbms_output.enable();
    dbms_output.put_line ('Test output'); 
END;

Complete Example and Best Practices

Let's refactor the previous discount procedure to demonstrate correct implementation:

create or replace PROCEDURE discount  
IS
    v_count NUMBER;
BEGIN
    -- Update DVD prices
    UPDATE dvd SET me_our_price = me_our_price * 0.90 WHERE me_release_year = 2011;
    UPDATE dvd SET me_our_price = me_our_price * 0.80 WHERE me_release_year = 2010;
    
    -- Update Blu-ray prices
    UPDATE bluray SET me_our_price = me_our_price * 0.95 WHERE me_release_year = 2011;
    UPDATE bluray SET me_our_price = me_our_price * 0.90 WHERE me_release_year = 2010;
    
    -- Output Blu-ray movie information
    DBMS_OUTPUT.PUT_LINE('=== Blu-ray Movie Price List ===');
    FOR rec IN (SELECT e.mo_title, e.mo_bluray.me_list_price, e.mo_bluray.me_our_price 
                FROM movie e WHERE e.mo_bluray IS NOT NULL) 
    LOOP 
        DBMS_OUTPUT.PUT_LINE(rec.mo_title || ' - Original Price: ' || rec.me_list_price 
                           || ', Current Price: ' || rec.me_our_price); 
    END LOOP; 
    
    -- Output DVD movie information
    DBMS_OUTPUT.PUT_LINE('=== DVD Movie Price List ===');
    FOR rec IN (SELECT e.mo_title, e.mo_dvd.me_list_price, e.mo_dvd.me_our_price 
                FROM movie e WHERE e.mo_dvd IS NOT NULL) 
    LOOP 
        DBMS_OUTPUT.PUT_LINE(rec.mo_title || ' - Original Price: ' || rec.me_list_price 
                           || ', Current Price: ' || rec.me_our_price); 
    END LOOP; 
    
    -- Output update statistics
    SELECT COUNT(*) INTO v_count FROM dvd WHERE me_release_year IN (2010, 2011);
    DBMS_OUTPUT.PUT_LINE('Updated ' || v_count || ' DVD records');
    
    SELECT COUNT(*) INTO v_count FROM bluray WHERE me_release_year IN (2010, 2011);
    DBMS_OUTPUT.PUT_LINE('Updated ' || v_count || ' Blu-ray records');
    
END discount;

Performance Considerations and Limitations

When using DBMS_OUTPUT, be aware of the following limitations:

• Buffer size limit: Default 20000 bytes, adjustable via DBMS_OUTPUT.ENABLE(buffer_size)
• Session-level setting: SET SERVEROUTPUT ON needs to be set only once
• Debugging purpose: DBMS_OUTPUT is primarily for debugging, use cautiously in production
• Performance impact: Heavy output may affect performance, recommended for development/testing

Conclusion

Understanding the correct usage of SET SERVEROUTPUT ON is crucial for Oracle developers. The key point to remember is that this is a SQL*Plus environment command, not part of PL/SQL syntax. By properly configuring the session environment and using the DBMS_OUTPUT package appropriately, developers can effectively debug and monitor output during development. Mastering these concepts not only helps avoid compilation errors but also improves development efficiency and code quality.