PL/SQL Anonymous Block

Introduction to PL/SQL Anonymous Block

The following article provides an outline for PL/SQL Anonymous Block. Normally PL/SQL contains the block structure that means code is organized into blocks. When we write the block without a name, we can call it an anonymous block. We cannot save on the oracle database server due to this reason it is just for one-time use. Because of this reason PL/SQL anonymous blocks are useful for testing purposes.

In PL/SQL block it contains the three different parts such as the declaration, executable, and exception handling part or we can say those sections. Apart from this three-section execution section is compulsory and the remaining two sections, namely the declaration and exception handling are optional parts. This block name we can store into the oracle database server and we are able to reuse it whenever we require it.

Syntax of PL/SQL Anonymous Block

Given below is the syntax mentioned:

BEGIN
Expression 1;
Expression 2;
Expression N;
END;

Explanation:

• In the above syntax, we start with the BEGIN keyword and end with the END keyword as shown, inside the BEGIN and END we write a different expression or we can say that statement as per requirement.
• In this syntax, the BEGIN keyword shows the executable section of the block, and the END keyword shows the end of the block.

How does Anonymous Block work in PL/SQL?

Given below shows how an anonymous block works in PL/SQL:

An anonymous block is a PL/SQL block that has no name attached to it. They must be generated and utilized in the same session because they will not be saved as database objects on the server. They don’t require any compilation stages because they don’t need to be stored in a database. They are directly written and executed, with compilation and execution occurring in the same process.

Basically anonymous consists of three different sections as follows.

• Declaration section of anonymous block: In this section, Pl/SQL allows us to declare variables and define the data types, it also allows us to allocate the memory space for cursors as per our requirement and its optional section of the block.
• Execution section of anonymous block: This is the second section of the PL/SQL block, the execution section always starts with the BEGIN keyword and ends with the END keyword. In this block, we just need to write at least one executable expression even if it is null, which means it is a mandatory section of the PL/SQL block.
• Exception handling section of anonymous block: Pl/SQL block contains the exception handling section and it starts with the EXCEPTION keyword. By using this section we can control the block.

Some important characteristics of the anonymous block as follows:

• There is no reference name given for these blocks.
• The term ‘DECLARE’ or ‘BEGIN’ is used to start these blocks.
• These blocks can’t be saved for subsequent use since they don’t have a reference name. They must be designed and implemented in the same session.
• They can call other named blocks, but they can’t call anonymous blocks since they don’t have any references.
• It can include nested blocks that are either named or anonymous. It may be nested in any block as well.
• These blocks can include all three components, with the execution portion being required and the other two sections being optional.

Examples of PL/SQL Anonymous Block

Given below are the examples of PL/SQL Anonymous Block:

Example #1

A very simple example of an anonymous block in SQL* Plus as follows.

Code:

SET SERVEROUTPUT ON;
BEGIN
DBMS_OUTPUT.put_line('Welcome Anonymous');
END;
/

Explanation:

• First, we need to connect the oracle database server using username and password. After that, we need to turn on the server output option by using the SET SERVEROUTPUT ON command so we can display the procedure.
• In the above example, we start to write the block with the BEGIN keyword and end with the END keyword as shown in the above block. Inside the block, we write the statement that contains the execution section of the block. The DBMS_OUTPUT.put_line statement is used as an output statement that means here we display the message by using this procedure and execution of block we use forward-slash (/) that instructs SQL * Plus to execute the block.
• The final output of the above block or procedure we illustrated by using the following screenshot as follows.

Output:

Example #2

Now let see more examples of PL/SQL anonymous blocks as follows.

Code:

SET SERVEROUTPUT ON;
DECLARE
VAL VARCHAR2(255) := 'Welcome Anonymous1';
BEGIN
DBMS_OUTPUT.put_line(VAL);
END;
/

Explanation:

• The above block we executed on the SQL*plus, so we need to follow the same process that we already mentioned in the above command. In this example we add the DECLARE section as shown in the above block, here we declare the VAL variable and its data type is varchar. The VAL variable holds the greeting message.
• After that, we wrote the execution section that is the DBMS_OUTPUT.put_line procedure to display the message of the VAL variable instead of a string. The forward-slash (/) is used to execute the block.
• The final output of the above block or procedure we illustrated by using the following screenshot as follows.

Output:

Example #3

Let’s see how we can handle the exception in PL/SQL block as follows.

Code:

SET SERVEROUTPUT ON;
DECLARE
VAL_R NUMBER;
BEGIN
VAL_R := 1/0;
EXCEPTION
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.put_line(SQLERRM);
END;
/

Explanation:

• By using the above example we try to handle the exception in the block. Similarly here we set the server output command by using SET SERVEROUTPUT ON command. After that, we write the DECLARE section, and inside that section, we declare the VAL_R variable with the number data type.
• In the next section, which is the execution section, we write the expression for the divide by zero exception with the EXCEPTION keyword as shown in the above example. After execution of this block, it shows a message like a divisor is equal to zero. The final output of the above block or procedure we illustrated by using the following screenshot as follows.

Output:

So in this way, we can also implement a nested anonymous block.

Conclusion

From the above article, we have seen the basic syntax of anonymous block and we also saw different examples of the anonymous block. From this article, we saw how and when we use PL/SQL anonymous block.

Recommended Articles

We hope that this EDUCBA information on “PL/SQL Anonymous Block” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

1. Triggers in PL/SQL
2. PL/SQL Collections
3. PL/SQL Data Types
4. Loops in PL/SQL