DBMS_OUTPUT:
Package DBMS_OUTPUT enables the output to be displayed from PL/SQL blocks and subprograms that makes it simpler to debug and test them. A put_line procedure outputs information to a buffer in the SGA. The information is displayed through calling the procedure get_line or through setting SERVEROUTPUT ON in SQL*Plus or Enterprise Manager.
For instance, let consider the following stored procedure is created.
CREATE PROCEDURE calc_payroll (payroll IN OUT REAL) AS CURSOR c1 IS SELECT sal,comm FROM emp;#create procedure and select sal and comm. From employee table #
BEGIN
payroll := 0;
FOR c1rec IN c1 LOOP
c1rec.comm := NVL(c1rec.comm, 0);
payroll := payroll + c1rec.sal + c1rec.comm;
END LOOP;
/* Display debug info. */
dbms_output.put_line('payroll: ' || TO_CHAR(payroll));
END calc_payroll;
When the subsequent commands are issued. SQL*Plus shows the value of payroll calculated through the procedure:
SET SERVEROUTPUT ON VARIABLE num NUMBER
EXECUTE calc_payroll(:num)
This would display payroll: 33525