Summary: in this tutorial, you will learn about the PL/SQL anonymous block and how to execute it using SQL*Plus and SQL Developer tools.
PL/SQL anonymous block overview
PL/SQL is a block-structured language whose code is organized into blocks. A PL/SQL block consists of three sections: declaration, executable, and exception-handling sections. In a block, the executable section is mandatory while the declaration and exception-handling sections are optional.
A PL/SQL block has a name. Functions or Procedures is an example of a named block. A named block is stored into the Oracle Database server and can be reused later.
A block without a name is an anonymous block. An anonymous block is not saved in the Oracle Database server, so it is just for one-time use. However, PL/SQL anonymous blocks can be useful for testing purposes.
The following picture illustrates the structure of a PL/SQL block:

1) Declaration section
A PL/SQL block has a declaration section where you declare variables, allocate memory for cursors, and define data types.
2) Executable section
A PL/SQL block has an executable section. An executable section starts with the keyword BEGIN
and ends with the keyword END
. The executable section must have a least one executable statement, even if it is the NULL
statement which does nothing.
3) Exception-handling section
A PL/SQL block has an exception-handling section that starts with the keyword EXCEPTION
. The exception-handling section is where you catch and handle exceptions raised by the code in the execution section.
Note a block itself is an executable statement, therefore you can nest a block within other blocks.
PL/SQL anonymous block example
The following example shows a simple PL/SQL anonymous block with one executable section.
BEGIN
DBMS_OUTPUT.put_line ('Hello World!');
END;
Code language: SQL (Structured Query Language) (sql)
The executable section calls the DMBS_OUTPUT.PUT_LINE
procedure to display the "Hello World"
message on the screen.
Execute a PL/SQL anonymous block using SQL*Plus
Once you have the code of an anonymous block, you can execute it using SQL*Plus, which is a command-line interface for executing SQL statement and PL/SQL blocks provided by Oracle Database.
The following picture illustrates how to execute a PL/SQL block using SQL*Plus:

First, connect to the Oracle Database server using a username and password.
Second, turn on the server output using the SET SERVEROUTPUT ON
command so that the DBMS_OUTPUT.PUT_LINE
procedure will display text on the screen.
Third, type the code of the block and enter a forward slash ( /
) to instruct SQL*Plus to execute the block. Once you type the forward-slash (/), SQL*Plus will execute the block and display the Hello World
message on the screen as shown in the illustrations.
Note that you must execute SET SERVEROUTPUT ON
command in every session that you connect to the Oracle Database in order to show the message using the DBMS_OUTPUT.PUT_LINE
procedure.
To execute the block that you have entered again, you use /
command instead of typing everything from the scratch:

If you want to edit the code block, use the edit
command. SQL*Plus will write the code block to a file and open it in a text editor as shown in the following picture:

You can change the contents of the file like the following:
begin
dbms_output.put_line('Hello There');
end;
/
Code language: SQL (Structured Query Language) (sql)
And save and close the file. The contents of the file will be written to the buffer and recompiled.
After that, you can execute the code block again, it will use the new code:

Execute a PL/SQL anonymous block using SQL Developer
First, connect to the Oracle Database server using Oracle SQL Developer.
Second, create a new SQL file named anonymous-block.sql
resided in the C:\plsql
directory that will store the PL/SQL code.


Third, enter the PL/SQL code and execute it by clicking the Execute button or pressing the Ctrl-Enter keyboard shortcut.

More PL/SQL anonymous block examples
In this example, we first declare a variable l_message
that holds the greeting message. And then, in the execution section, we use the DBMS_OUTPUT.PUTLINE
procedure to show the content of this variable instead of using a literal string.
DECLARE
l_message VARCHAR2( 255 ) := 'Hello World!';
BEGIN
DBMS_OUTPUT.PUT_LINE( l_message );
END;
Code language: SQL (Structured Query Language) (sql)
Here is the output:
Hello World!
The next anonymous block example adds an exception-handling section which catches ZERO_DIVIDE
exception raised in the executable section and displays an error message.
DECLARE
v_result NUMBER;
BEGIN
v_result := 1 / 0;
EXCEPTION
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE( SQLERRM );
END;
Code language: SQL (Structured Query Language) (sql)
The error mesage is:
ORA-01476: divisor is equal to zero
Now, you should know how to create PL/SQL anonymous blocks and execute them using SQL*Plus and Oracle SQL Developer tools.