Executing PL/SQL Code

Contact Us or call 1-877-932-8228
Executing PL/SQL Code

Executing PL/SQL Code

The following demonstrates how to execute an Oracle PL/SQL stored procedure.

Code Sample:

exec dbms_output.put_line('Hello World - will not print out');
-- Now turn server output on
exec dbms_output.put_line('Hello World');

Code Explanation

A stored procedure is one type of PL/SQL programming unit. This example demonstrates one way that a stored procedure can be executed. DBMS_OUTPUT is an Oracle supplied PL/SQL package that contains a stored procedure named PUT_LINE. It puts data into a buffer on the server. However, by default both SQL*Plus and SQL Developer suppress this output. Whenever you are interested in seeing output displayed you would call this procedure from your program using dot notation: DBMS_OUTPUT.PUT_LINE('Message to display').

However, in order to actually see the message displayed in SQL*Plus you must first issue a SET SERVEROUTPUT ON command. (You'll see a demonstration in the next section for displaying output in SQL Developer.) The SET SERVEROUTPUT ON command causes the client application to issue a call to another stored procedure (DBMS_OUTPUT.GET_LINE) behind the scenes that retrieves the data from the buffer and displays it. In the next section you'll see that using SET SERVEROUTPUT ON is not necessary when SQL Developer is used.