Oracle PL/SQL Tutorial/PL SQL Programming/DBMS OUTPUT

Call user defined function in DBMS_OUTPUT.PUT_LINE

SQL> set serveroutput on
SQL> set echo on
SQL>
SQL> CREATE OR REPLACE FUNCTION squareme(thenum number)
  2       RETURN NUMBER IS
  3  BEGIN
  4       RETURN thenum * thenum;
  5  END squareme;
  6  /
Function created.
SQL>
SQL> BEGIN
  2       DBMS_OUTPUT.PUT_LINE("9 squared is " || squareme(9) );
  3  END;
  4  /
9 squared is 81
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>

DBMS_OUTPUT.CHARARR

SQL>
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2    v_Data      DBMS_OUTPUT.CHARARR;
  3    v_NumLines  NUMBER;
  4  BEGIN
  5    -- Enable the buffer first.
  6    DBMS_OUTPUT.ENABLE(1000000);
  7
  8    DBMS_OUTPUT.PUT_LINE("Line One");
  9    DBMS_OUTPUT.PUT_LINE("Line Two");
 10    DBMS_OUTPUT.PUT_LINE("Line Three");
 11
 12    -- Set the maximum number of lines which we want to retrieve.
 13    v_NumLines := 3;
 14
 15    DBMS_OUTPUT.GET_LINES(v_Data, v_NumLines);
 16
 17    FOR v_Counter IN 1..v_NumLines LOOP
 18      DBMS_OUTPUT.put_line(v_Data(v_Counter));
 19    END LOOP;
 20  END;
 21  /
Line One
Line Two
Line Three
PL/SQL procedure successfully completed.
SQL>

DBMS_OUTPUT.GET_LINES

SQL>
SQL>
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2    v_Data      DBMS_OUTPUT.CHARARR;
  3    v_NumLines  NUMBER;
  4  BEGIN
  5    -- Enable the buffer first.
  6    DBMS_OUTPUT.ENABLE(1000000);
  7
  8    DBMS_OUTPUT.PUT_LINE("Line One");
  9    DBMS_OUTPUT.PUT_LINE("Line Two");
 10    DBMS_OUTPUT.PUT_LINE("Line Three");
 11
 12    v_NumLines := 3;
 13
 14    DBMS_OUTPUT.GET_LINES(v_Data, v_NumLines);
 15
 16    FOR v_Counter IN 1..v_NumLines LOOP
 17      DBMS_OUTPUT.put_line(v_Data(v_Counter));
 18    END LOOP;
 19  END;
 20  /
Line One
Line Two
Line Three
PL/SQL procedure successfully completed.
SQL>

New quoting syntax is q"[ ... ]" where the [] is the user-defined delimiter.

SQL> --The [] can be any character not present in the string literal.
SQL>
SQL> BEGIN
  2     DBMS_OUTPUT.PUT_LINE("Oracle""s  are BEAUTIFUL");
  3     DBMS_OUTPUT.PUT_LINE(q"!Oracle"s are BEAUTIFUL!");
  4     DBMS_OUTPUT.PUT_LINE(q"[Oracle"s are BEAUTIFUL]");
  5     DBMS_OUTPUT.PUT_LINE(q"<Oracle"s are BEAUTIFUL>");
  6     DBMS_OUTPUT.PUT_LINE(q"%Oracle"s are BEAUTIFUL%");
  7  END;
  8  /
Oracle"s  are BEAUTIFUL
Oracle"s are BEAUTIFUL
Oracle"s are BEAUTIFUL
Oracle"s are BEAUTIFUL
Oracle"s are BEAUTIFUL
PL/SQL procedure successfully completed.
SQL>

Output NUMBER with dbms_output.put_line Procedure

SQL>
SQL> SET SERVEROUTPUT ON
SQL> SET ECHO ON
SQL>
SQL>
SQL> DECLARE
  2    x     NUMBER;
  3  BEGIN
  4    x := 72600;
  5    dbms_output.put_line("The variable  X = ");
  6    dbms_output.put_line(x);
  7  END;
  8  /
The variable  X =
72600
PL/SQL procedure successfully completed.
SQL>

Output string with dbms_output.put_line Procedure

SQL>
SQL> SET SERVEROUTPUT ON
SQL> SET ECHO ON
SQL>
SQL>
SQL> DECLARE
  2    x     NUMBER;
  3  BEGIN
  4    x := 72600;
  5    dbms_output.put_line("The variable  X = ");
  6    dbms_output.put_line(x);
  7  END;
  8  /
The variable  X =
72600
PL/SQL procedure successfully completed.
SQL>

PL/SQL Block Showing the Use of the dbms_output.put_line Procedure

SQL>
SQL> SET SERVEROUTPUT ON
SQL> SET ECHO ON
SQL>
SQL>
SQL> DECLARE
  2    x     NUMBER;
  3  BEGIN
  4    x := 72600;
  5    dbms_output.put_line("The variable  X = ");
  6    dbms_output.put_line(x);
  7  END;
  8  /
The variable  X =
72600
PL/SQL procedure successfully completed.
SQL>

Use the LOWER function to in DBMS_OUTPUT.PUT_LINE

SQL>
SQL>
SQL> SET SERVEROUTPUT ON
SQL> BEGIN
  2     DBMS_OUTPUT.PUT_LINE(LOWER("CaSe Is nOt"));
  3  END;
  4  /
case is not
PL/SQL procedure successfully completed.
SQL>
SQL>

Use the ROUND function in PL/SQL

SQL>
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2     v_round NUMBER (10,4) := 12345.6789;
  3  BEGIN
  4
  5     DBMS_OUTPUT.PUT_LINE("Default: "||ROUND(v_round));
  6     DBMS_OUTPUT.PUT_LINE("+2: "||ROUND(v_round, 2));
  7     DBMS_OUTPUT.PUT_LINE("-2: "||ROUND(v_round, -2));
  8
  9  END;
 10  /
Default: 12346
+2: 12345.68
-2: 12300
PL/SQL procedure successfully completed.
SQL>
SQL>

Using two single-quotes print the string correctly

SQL>
SQL> SET SERVEROUTPUT ON
SQL>
SQL> BEGIN
  2     DBMS_OUTPUT.PUT_LINE("Oracle""s are BEAUTIFUL");
  3  END;
  4  /
Oracle"s are BEAUTIFUL
PL/SQL procedure successfully completed.
SQL>