SQL Plus - Serveroutput system variable (Enabling DBMS_OUTPUT.PUT_LINE)

> Procedural Languages > (SQL Plus|SqlCl) (Oracle Database Console)

1 - About

serveroutput is a system variable.

This variable controls whether to display output (that is, DBMS_OUTPUT.PUT_LINE) of stored procedures or PL/SQL blocks in SQL*Plus.

Resources are not pre-allocated when SERVEROUTPUT is set.

Advertising

3 - Example

SET SERVEROUTPUT ON
BEGIN
DBMS_OUTPUT.PUT_LINE ('hello');
END;
hello

4 - Side Effect

4.1 - Last Query is no more what you expect

When enabled, serveroutput will add this extra statement after your query:

BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;

It means that the last session query will always be this one. You can check it with this query

SELECT
  sql_text
FROM
  v$sql
WHERE
  sql_id =
  (
    SELECT
      prev_sql_id
    FROM
      V$session
    WHERE
      sid = SYS_CONTEXT( 'USERENV', 'SID' )
  );

5 - Documentation / Reference

lang/sqlplus/serveroutput.txt · Last modified: 2017/05/09 16:51 by gerardnico