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>

CASE

Tested on an Oracle 11 database. The CASE expression allows you to add conditional tests to SQL statements. First create a table with a few rows of test data:

SQL> create table test_segments as
  2  select *
  3  from dba_segments where 1=2
  4  /

Table created.

SQL> insert into test_segments
  2  select * from dba_segments
  3  where bytes < 100000
  4  and owner = 'SYS'
  5  and rownum < 6
  6  /

5 rows created.

SQL> insert into test_segments
  2  select * from dba_segments
  3  where bytes >= 100000
  4  and bytes < 150000
  5  and owner = 'SYS'
  6  and rownum < 6
  7  /

5 rows created.

SQL> insert into test_segments
  2  select * from dba_segments
  3  where bytes >= 150000
  4  and owner = 'SYS'
  5  and rownum < 6
  6  /

5 rows created.

SQL>

A very simple CASE expression might have a couple of WHEN conditions. If none of these are satisfied, the output will be blank:

SQL> select segment_name,
  2  case
  3   when bytes < 100000 then 'SMALL'
  4   when bytes < 150000 then 'MEDIUM'
  5  end as object_size
  6  from test_segments
  7  order by 1
  8  /

SEGMENT_NAME                   OBJECT_SIZE
------------------------------ -----------
C_USER#
I_CCOL1
I_CDEF3                        SMALL
I_COBJ#                        MEDIUM
I_COL3
I_FILE1                        SMALL
I_IND1                         MEDIUM
I_OBJ1
I_OBJ2
I_TAB1                         SMALL
I_TS#                          SMALL
I_USER#                        MEDIUM
I_USER2                        MEDIUM
I_VIEW1                        MEDIUM
UNDO$                          SMALL

15 rows selected.

SQL> 

To get round this, you can add an ELSE at the end, if you wish:

SQL> select segment_name,
  2  case
  3   when bytes < 100000 then 'SMALL'
  4   when bytes < 150000 then 'MEDIUM'
  5   else 'LARGE'
  6  end as object_size
  7  from test_segments
  8  order by 1
  9  /

SEGMENT_NAME                   OBJECT_SIZE
------------------------------ -----------
C_USER#                        LARGE
I_CCOL1                        LARGE
I_CDEF3                        SMALL
I_COBJ#                        MEDIUM
I_COL3                         LARGE
I_FILE1                        SMALL
I_IND1                         MEDIUM
I_OBJ1                         LARGE
I_OBJ2                         LARGE
I_TAB1                         SMALL
I_TS#                          SMALL
I_USER#                        MEDIUM
I_USER2                        MEDIUM
I_VIEW1                        MEDIUM
UNDO$                          SMALL

15 rows selected.

SQL>

And you can even have nested CASE expressions. Also note how the CASE may, or may not, be followed immediately by a column name:

SQL> select segment_name,
  2  case segment_type
  3   when 'TABLE' then
  4   case
  5    when bytes < 100000 then 'SMALL TABLE'
  6    when bytes < 150000 then 'MEDIUM TABLE'
  7    else 'LARGE TABLE'
  8   end
  9   when 'INDEX' then
 10   case
 11    when bytes < 100000 then 'SMALL INDEX'
 12    when bytes < 150000 then 'MEDIUM INDEX'
 13    else 'LARGE INDEX'
 14   end
 15  end as size_and_type
 16  from
 17  test_segments
 18  order by 1
 19  /

SEGMENT_NAME                   SIZE_AND_TYPE
------------------------------ -------------
C_USER#
I_CCOL1                        LARGE INDEX
I_CDEF3                        SMALL INDEX
I_COBJ#                        MEDIUM INDEX
I_COL3                         LARGE INDEX
I_FILE1                        SMALL INDEX
I_IND1                         MEDIUM INDEX
I_OBJ1                         LARGE INDEX
I_OBJ2                         LARGE INDEX
I_TAB1                         SMALL INDEX
I_TS#                          SMALL INDEX
I_USER#                        MEDIUM INDEX
I_USER2                        MEDIUM INDEX
I_VIEW1                        MEDIUM INDEX
UNDO$                          SMALL TABLE

15 rows selected.

SQL>