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 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
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;
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
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; /
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;
Here is the output:
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;
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.