1-2. Executing a Block of Code in SQL*Plus
Problem
You want to execute a block of PL/SQL code within the SQL*Plus command-line utility.
Solution
The solution to this recipe is multitiered, in that executing a block of code in SQL*Plus incorporates at least two steps:
Enter the PL/SQL code into the SQL*Plus utility.
Execute the code block by simply placing a backslash (/) as the last line of code, and then press the Enter key.
The following is an example displaying a code block that has been typed into SQL*Plus:
SQL> BEGIN
2 DBMS_OUTPUT.PUT_LINE('HELLO WORLD'),
3 END;
4 /
How It Works
To execute code within SQL*Plus, you simply type the executable block and place a forward slash (/) after the closing END. The code will be executed by the SQL*Plus interpreter when the slash is encountered. Once the code has been executed, control will be returned to the user at the SQL*Plus prompt. This differs from the execution of a query within SQL*Plus because when you write a SELECT statement, it can be executed by simply placing a semicolon at the end and hitting the Enter key.
Note Be sure to put the forward slash on a line by itself and to make it the first character on that line.
If the code you are executing contains a DECLARE section, then its execution will resemble the following:
SQL> DECLARE
2 -- Some cursor and variable declarations
3 BEGIN
4 DBMS_OUTPUT.PUT_LINE('Hello World'),
5 END;
6 /
Hello World
PL/SQL procedure successfully completed.
You also follow a similar syntax when creating stored procedures, packages, and functions. To create or replace stored code, write a CREATE statement and use a trailing slash, followed by pressing the Enter key. For example, the you can use the following code to create a simple stored procedure that prints a line of text. Notice how it contains a trailing slash character.
SQL> CREATE OR REPLACE PROCEDURE hello_world AS
2 BEGIN
3 DBMS_OUTPUT.PUT_LINE('Hello World'),
4 END;
5 /
Procedure created.
Most likely, you will use SQL*Plus for much of your development life cycle. It is easy to execute code blocks and create stored code using the syntax discussed in this recipe. The same syntax can also be carried over to the Oracle Application Express environment. The Oracle Application Express environment contains an embedded SQL*Plus interpreter that can be used for performing the same tasks that you would perform using the standard client. For more information about using Oracle Application Express for building and maintaining web applications, please see the online Oracle documentation at http://download.oracle.com/docs/cd/E11882_01/appdev.112/e11946/toc.htm.