SERVEROUTPUT

Set serveroutput off/on can be used in SQL*Plus to control the output from the PL/SQL dbms_output.put_line statement. When you first login the default is for serveroutput to be off:

SQL> show serveroutput

serveroutput OFF

SQL>

If serveroutput is set to off, you will not get any output from dbms_output.put_line:

SQL> begin

  2  dbms_output.put_line('12345678901234567890');

  3  end;

  4  /

 

PL/SQL procedure successfully completed.

 

SQL>

Setting it to on changes this:

SQL> set serveroutput on

SQL> show serveroutput

serveroutput ON size 2000 format WORD_WRAPPED

SQL> begin

  2  dbms_output.put_line('12345678901234567890');

  3  end;

  4  /

12345678901234567890

 

PL/SQL procedure successfully completed.

 

SQL>

There is also a size associated with serveroutput. This specifies the volume of output allowed. In Oracle 9 this can have any value between 2000 and 1000000:

SQL> set serveroutput on size 1999

SP2-0547: size option 1999 out of range (2000 through 1000000)

SQL> set serveroutput on size 1000001

SP2-0547: size option 1000001 out of range (2000 through 1000000)

SQL>

I will set it to 2000 for the example below although, as you can see above, this appears to be the default. When you reach the limit set by the size parameter, Oracle displays an error and stops:

SQL> set serveroutput on size 2000

SQL> declare

  2  a number;

  3  begin

  4  a := 1;

  5  while a < 100

  6  loop

  7  dbms_output.put_line(a||' Andrew Reid, International DBA');

  8  a := a + 1;

  9  end loop;

10  end;

11  /

1 Andrew Reid, International DBA

2 Andrew Reid, International DBA

3 Andrew Reid, International DBA

4 Andrew Reid, International DBA

5 Andrew Reid, International DBA

6 Andrew Reid, International DBA

7 Andrew Reid, International DBA

8 Andrew Reid, International DBA

9 Andrew Reid, International DBA

10 Andrew Reid, International DBA

11 Andrew Reid, International DBA

12 Andrew Reid, International DBA

13 Andrew Reid, International DBA

14 Andrew Reid, International DBA

15 Andrew Reid, International DBA

16 Andrew Reid, International DBA

17 Andrew Reid, International DBA

18 Andrew Reid, International DBA

19 Andrew Reid, International DBA

20 Andrew Reid, International DBA

21 Andrew Reid, International DBA

22 Andrew Reid, International DBA

23 Andrew Reid, International DBA

24 Andrew Reid, International DBA

25 Andrew Reid, International DBA

26 Andrew Reid, International DBA

27 Andrew Reid, International DBA

28 Andrew Reid, International DBA

29 Andrew Reid, International DBA

30 Andrew Reid, International DBA

31 Andrew Reid, International DBA

32 Andrew Reid, International DBA

33 Andrew Reid, International DBA

34 Andrew Reid, International DBA

35 Andrew Reid, International DBA

36 Andrew Reid, International DBA

37 Andrew Reid, International DBA

38 Andrew Reid, International DBA

39 Andrew Reid, International DBA

40 Andrew Reid, International DBA

41 Andrew Reid, International DBA

42 Andrew Reid, International DBA

43 Andrew Reid, International DBA

44 Andrew Reid, International DBA

45 Andrew Reid, International DBA

46 Andrew Reid, International DBA

47 Andrew Reid, International DBA

48 Andrew Reid, International DBA

49 Andrew Reid, International DBA

50 Andrew Reid, International DBA

51 Andrew Reid, International DBA

52 Andrew Reid, International DBA

53 Andrew Reid, International DBA

54 Andrew Reid, International DBA

declare

*

ERROR at line 1:

ORA-20000: ORU-10027: buffer overflow, limit of 2000

bytes

ORA-06512: at "SYS.DBMS_OUTPUT", line 35

ORA-06512: at "SYS.DBMS_OUTPUT", line 198

ORA-06512: at "SYS.DBMS_OUTPUT", line 139

ORA-06512: at line 7

 

SQL>