PL/SQL Anonymous Block

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;

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;
/

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:

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;

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.