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 block consists of three sections:

1. Declaration
2. Executable
3. Exception-handling

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 in the Oracle Database server and can be reusable 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 a NULL statement that 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: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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 statements 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 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: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Here is the output:

Hello World!

The next anonymous block example adds an exception-handling section that 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: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The error message is:

ORA-01476: divisor is equal to zeroCode language: plaintext (plaintext)

Now, you should know how to create PL/SQL anonymous blocks and execute them using SQL*Plus and Oracle SQL Developer tools.