Oracle PL/SQL/SQL Plus/SERVEROUTPUT

Make sure SQL*Plus SERVEROUTPUT setting is on

   <source lang="sql">

SQL> SET SERVEROUTPUT ON SIZE 1000000 SQL> SQL> BEGIN

 2     DBMS_OUTPUT.PUT_LINE("hello, world");
 3  END;
 4  /

hello, world PL/SQL procedure successfully completed. SQL> SQL>

 </source>
   
  

SET SERVEROUTPUT OFF/ON

   <source lang="sql">
 

SQL> SQL> SQL> SET ECHO ON SQL> SET SERVEROUTPUT OFF SQL> SQL> BEGIN

 2    DBMS_OUTPUT.ENABLE (2000);
 3    DBMS_OUTPUT.PUT_LINE("Three names will be written.");
 4    DBMS_OUTPUT.PUT("J");
 5    DBMS_OUTPUT.NEW_LINE;
 6    DBMS_OUTPUT.PUT("S");
 7    DBMS_OUTPUT.NEW_LINE;
 8    DBMS_OUTPUT.PUT("T");
 9    DBMS_OUTPUT.NEW_LINE;
10  END;
11  /

PL/SQL procedure successfully completed. SQL> SQL> SET SERVEROUTPUT ON SQL> DECLARE

 2    throw_away VARCHAR2(50);
 3    names DBMS_OUTPUT.CHARARR;
 4    lines_to_get NUMBER;
 5    inx1 NUMBER;
 6    combined_names VARCHAR2(80);
 7    status NUMBER;
 8  BEGIN
 9    DBMS_OUTPUT.GET_LINE(throw_away, status);
10
11    lines_to_get := 3;
12    DBMS_OUTPUT.GET_LINES(names, lines_to_get);
13
14    combined_names := "";
15    FOR inx1 IN 1 .. lines_to_get LOOP
16      IF inx1 > 1 THEN
17        combined_names := combined_names || " and ";
18      END IF;
19
20      combined_names := combined_names || names(inx1);
21    END LOOP;
22    DBMS_OUTPUT.PUT_LINE(combined_names);
23  END;
24  /

J and S and T PL/SQL procedure successfully completed. SQL> SQL> --

 </source>
   
  

SET SERVEROUTPUT ON SIZE 1000000

   <source lang="sql">

SQL> SET SERVEROUTPUT ON SIZE 1000000 SQL> BEGIN

 2    dbms_output.put_line("Hello World.");
 3  END;
 4  /

Hello World. PL/SQL procedure successfully completed. SQL>

 </source>
   
  

The SET SERVEROUTPUT Command

   <source lang="sql">
 

SQL> SET SERVEROUTPUT ON SQL> BEGIN

 2      dbms_output.put_line("This is the first line");
 3      dbms_output.put_line("This is the second line");
 4      dbms_output.put_line("This is the last line");
 5  END;
 6  /

This is the first line This is the second line This is the last line PL/SQL procedure successfully completed.

 </source>
   
  

TRUNCATED formatting option: each line of the displayed output is truncated exactly at the length specified by the LINESIZE variable.

   <source lang="sql">
 

SQL> SQL> SET SERVEROUTPUT ON FORMAT TRUNCATED SQL> SET LINESIZE 20 SQL> BEGIN

 2   DBMS_OUTPUT.PUT_LINE("After the first 20 characters please");
 3   DBMS_OUTPUT.PUT_LINE("continue on the second line");
 4  END;
 5  /

After the first 20 c continue on the seco PL/SQL procedure successfully completed. SQL>

 </source>
   
  

WORD_WRAPPED option for FORMAT wraps each line to the length specified by the value of the LINESIZE variable

   <source lang="sql">
 

SQL> SQL> SET SERVEROUTPUT ON FORMAT WORD_WRAPPED SQL> SET LINESIZE 20 SQL> BEGIN

 2      dbms_output.put_line("After the first 20 characters please");
 3      dbms_output.put_line("continue on the second line");
 4  END;
 5  /

After the first 20 characters please continue on the second line PL/SQL procedure successfully completed.

 </source>