Specifies whether output from the DBMS_OUTPUT message buffer is redirected to standard output.
EXECUTE privilege on the DBMS_OUTPUT module.
Database
SET SERVEROUTPUT ON@
DROP PROCEDURE proc1@
CREATE PROCEDURE proc1()
BEGIN
CALL DBMS_OUTPUT.PUT( 'p1 = ' || p1 );
CALL DBMS_OUTPUT.PUT( 'p2 = ' || p2 );
CALL DBMS_OUTPUT.NEW_LINE;
END@
CALL proc1( 10, 'Peter' )@
SET SERVEROUTPUT OFF@
This example results in the following output:SET SERVEROUTPUT ON
DB20000I The SET SERVEROUTPUT command completed successfully.
DROP PROCEDURE PROC1
DB20000I The SQL command completed successfully.
CREATE PROCEDURE proc1()
BEGIN
CALL DBMS_OUTPUT.PUT( 'p1 = ' || p1 );
CALL DBMS_OUTPUT.PUT( 'p2 = ' || p2 );
CALL DBMS_OUTPUT.NEW_LINE;
END@
DB20000I The SQL command completed successfully.
CALL proc1( 10, 'Peter' )@
Return Status = 0
p1 = 10
p2 = Peter
SET SERVEROUTPUT OFF
DB20000I The SET SERVEROUTPUT command completed successfully.
Messages are added to the DBMS_OUTPUT message buffer by the PUT, PUT_LINE, and NEW_LINE procedures.
When the command SET SERVEROUTPUT ON executes, it calls the DBMS_OUTPUT.ENABLE procedure with the default buffer size of 20000 bytes and sets an internal flag in the command line processor (CLP) or command line processor plus (CLPPlus). When this flag is enabled, the application calls the GET_LINES procedure after executing each SELECT or CALL statement, and redirects the messages from the message buffer to standard output. To increase the DBMS_OUTPUT buffer size, call DBMS_OUTPUT.ENABLE procedure with a larger buffer size after executing SET SERVER OUTPUT ON, for example: CALL DBMS_OUTPUT.ENABLE( 50000 );
When the command SET SERVEROUTPUT OFF executes: it calls the DBMS_OUTPUT.DISABLE procedure, messages that are in the message buffer are discarded, and calls to PUT, PUT_LINE, and NEW_LINE procedures are ignored. The DBMS_OUTPUT.GET_LINES procedure will not be called after each SELECT or CALL statement.