PL/SQL Debugging Statement

In PL/SQL debugging statement is used to display output or print the message.  DBMS_OUTPUT.PUT_LINE is used to display the message in fact.

DBMS_OUTPUT is a built in package that enables to send messages from stored procedures and packages etc.  PUT_LINE() is a procedure that used to identify the error during run time of the PL/SQL block.

PL/SQL Debugging statement Syntax

DBMS_OUTPUT.PUT_LINE(‘MESSAGE’|| VARIABLE_NAME); 

Sample Example:

BEGIN
	Dbms_Output.Put_Line('HELLO WORLD');
END;

Above statement will print
HELLO WORLD

DBMS_OUTPUT Subprograms

Below are DBMS_OUTPUT Subprograms that are used in Oracle SQL Developer and TOAD / UI

DBMS_OUTPUT.DISABLE; –disables the server to print the  output message
DBMS_OUTPUT.ENABLE; –enables the server to print the output message
DBMS_OUTPUT.NEW_LINE — returns the cursor into a new line
DBMS_OUTPUT.GET_LINE – it receives single line from buffered information
DBMS_OUTPUT.GET_LINES –it receives multiple lines from buffered information
DBMS_OUTPUT.PUT_LINE – IT IS USED TO PRINT THE MESSAGE

Examples using DBMS_OUTPUT Subprograms

Disabling Output Message

We can have the debug message in the program, but, we enable or disable deepening if we want to display the message or not. For example, we have used dbms_output.disable, so, program will not print the message.

BEGIN
   dbms_output.disable;--to disable the output of the message
   dbms_output.put_line('HELLO I AM A OUT PUT DEBUGGING STATEMENT');
END;

For the above no output is displayed because the “dbms_output.disable;” server disables the printing of output message.

Enabling Output Message

BEGIN
dbms_output.ENABLE;
dbms_output.put_line('HELLO I AM A OUT PUT DEBUGGING STATEMENT');
-
END;

OUTPUT
HELLO I AM A OUT PUT DEBUGGING STATEMENT

Inserting new line between tow messages

BEGIN
dbms_output.ENABLE;
dbms_output.put_line('HELLO I AM A OUT PUT DEBUGGING STATEMENT');
dbms_output.new_line; --enters into a new line
dbms_output.put_line('HELLO I AM A OUT PUT DEBUGGING STATEMENT');
END;

OUTPUT
HELLO I AM A OUT PUT DEBUGGING STATEMENT

HELLO I AM A OUT PUT DEBUGGING STATEMENT

IN SQL PLUS we can use the following commands to enable or disable the server to print the message.

• SET SERVER OUTPUT ON—enable the server to print the message
• SET SERVER OTUPUT ON SIZE 1000 — set sthe buffer size.
• SET SERVER OUTPUT OFF –disable the server to print the message.

A simple program to print the values using debugging statement.

Program to print addition of two numbers.

	DECLARE
           		 a number;
           		 b number;
           		 c number;
    	BEGIN
            	    a:=2;
            	    b:=2;
            	    c:=a+b;
    	 DBMS_OUTPUT.PUT_LINE('the value of c is='||c);
     	END;

OUTPUT

the value of c is=4