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.
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:
BEGIN
DBMS_OUTPUT.put_line ('Hello World!');
END;
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:
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.
begin
dbms_output.put_line('Hello There');
end;
/
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
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.
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.
