Tuesday, March 06, 2012

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>

No comments: