Ticker

6/recent/ticker-posts

Anonymous Block in PL/SQL with Example

In this PL/SQL Tutorial, We will explore about the Anonymous Block in PL/SQL.

Anonymous Block in PL/SQL


Before to learn about the anonymous block in PL/SQL. I will suggest to you please check our previous blog( Block Structure in PL/SQL).

In this Blog, we will understand Anonymous Block with the help of some Questions with Answers.

Let’s Start

Question 1:- What is Anonymous Block in PL/SQL?

Which block is not given any name is called Anonymous Block. 

Question 2:- Syntax of Anonymous Block

Declare

(Declaration Section)---Optional--

Begin

(Execution Section)---Necessary---

Exception

(Exception Handling Section)---Optional--

End;

Question 3:- Example of Anonymous Block in PL/SQL

Declare

V_NAME VARCHAR2 (10):=’PL/SQL Tutorial’;

Begin

DBMS_OUTPUT.PUT_LINE (V_NAME);

End;

OUTPUT:-PL/SQL Tutorial

(Note:-if block have any print statements like dbms_output.put_line('Testing') then Set Serveroutput on Statement always executed before the execution of  Block.)

Question 4 :- Why we do create an Anonymous Block in the PL/SQL?

Anonymous Block is used for the testing purposes.

Example 1:-

Let’s understand  If  you want to add some functionality in your existing code and before adding it to original code you want to test it so you can create anonymous block

if you find correct result  through that block then you can make that block to the Named Block or that logic used in your existing named block.

Example 2:You always write an anonymous block for testing your procedures.

May be both of the above example will help to you for the better understanding.

Question 5:- Is Anonymous block is as a database object?

No, Anonymous block does not save as a database object.

Question 6 :- Can we run anonymous block multiple time?

Yes, we can run anonymous block multiple time but with-in one session.

Question 7:- Can we run Named Block in the Anonymous block?

Yes, we can run Named Block in the Anonymous Block

Example:- First we will create a simple procedure which insert empno to the employee table then we call that procedure into the Anonymous Block.

Create or replace insert_data

as

begin

insert into emp(emp_no) values('100');

end;

/

Begin

insert_data;----this is my procedure name---

End;

May be, The above question and answer will help you for the better understanding about the Anonymous block in PL/SQL with Example.

If this blog post helps to you then please share your comments in the comment box. It will help to improve our confidence.

Post a Comment

0 Comments