Today we will learn how to create a PL/SQL block using Execute Immediate of Dynamic SQL in Oracle Database. In the last couple of weeks we have learnt a lot about dynamic SQL now I think it’s time to move on to dynamic PL/SQL.
Here we will do two demonstrations. In the first one we will learn how to create an anonymous PL/SQL block and in the second one we will create a named PL/SQL block. We will do both these using Execute Immediate Statement of Dynamic SQL.
This tutorial will require a working knowledge of Dynamic SQL and PL/SQL blocks thus I’ll suggest you to read the introduction of PL/SQL blocks and Dynamic SQL.
Now let’s start with the first demonstration and learn how to create an anonymous PL/SQL block using Dynamic SQL.
Step1: Prepare the PL/SQL block for Dynamic SQL Execution.
Though it is not mandatory but it’s a good practice to prepare beforehand your Query or Block which you want to execute with Execute Immediate of Dynamic SQL. Keeping that in mind let’s create a simple PL/SQL block.
DECLARE
var_user VARCHAR2(30);
BEGIN
SELECT user INTO var_user FROM dual;
DBMS_OUTPUT.PUT_LINE (''Current User is ''||var_user);
END;
Here we have prepared a very simple anonymous PL/SQL block. We will execute this block using the Execute Immediate of Dynamic SQL. Before learning how to do that let’s quickly see what is happening here in this PL/SQL Block.
In declaration section we have declared only one variable var_user of varchar2 datatype and in the execution section we have only two executable statements. The first statement is a SELECT-INTO statement which is returning and storing the current user through which we are connected to the database into our variable var_user.
The second statement is a simple output statement which is displaying back the username which the above SELECT-INTO statement stored into the variable var_user. On successful execution it will show you, the username using which you are connected to your database. This is nothing but a simple PL/SQL block.
Now let’s move on to the second step.
Step 2: Write the Dynamic SQL program.
Once you have created and checked your query or block next you have to write a PL/SQL program for dynamic SQL.
SET SERVEROUTPUT ON;
DECLARE
plsql_blk VARCHAR2 (250);
BEGIN
plsql_blk := 'DECLARE
var_user VARCHAR2 (10);
BEGIN
SELECT user INTO var_user FROM dual;
DBMS_OUTPUT.PUT_LINE (''Current User is ''||var_user);
END;';
EXECUTE IMMEDIATE plsql_blk;
END;
/
In the declaration section, once again we have declared only one variable plsql_blk. This variable will be used for holding the PL/SQL block which we will be running with the Execute immediate statement. As the Execute Immediate accepts only arguments of VARCHAR2 type thus we have declared plsql_blk as varchar2 datatype. Moreover this variable will be used for holding the entire PL/SQL block. Thus it should have sufficient data width.
In the execution section we have only two executable statements.
Statement 1: Assignment Statement (Optional)
The first statement is an assignment statement. Here we are assigning the PL/SQL block, prepared earlier in step 1, into a variable ‘plsql_blk’. Always make sure to enclose your query or block within a pair of single quotes. In addition end the assignment statement with a semi colon.
Watch the Video tutorial and learn how to resolve the conflicts caused by multiple single quotes.
Info:
Unlike SQL statement, you are allowed to end your PL/SQL block that you want to run with execute immediate statement with a semi colon. However you are not allowed to put the forward slash at the end of the a PL/SQL block, which we are generally used to.
Statement 2: Execute Immediate Statement.
The second statement is an Execute Immediate statement of Dynamic SQL. Here you write the reserved phrase EXECUTE IMMEDIATE followed by the name of the variable where your block is stored. Followed by a semi colon at the end of the statement.
Step 3: Execute your Dynamic SQL program
Once you are done writing your code, it means you are all set to execute it and see the result. The successful execution of the above program will show you the user name that connects you to your database.
That’s how we create an anonymous PL/SQL block using Execute Immediate of Dynamic SQL.
Now let’s learn how to create a named PL/SQL block using the same.
Step1: Prepare the named PL/SQL block for Dynamic SQL Execution.
For this demonstration we will use PL/SQL function as named PL/SQL block. Although you can use any named PL/SQL block yet the process will remain the same. For this demonstration we will create a PL/SQL function. It will return the area of circle using dynamic SQL.
CREATE OR REPLACE FUNCTION circle_area (radius NUMBER)
RETURN NUMBER IS
pi CONSTANT NUMBER(7,2) := 3.141;
area NUMBER(7,2);
BEGIN
area := pi * ( radius * radius );
RETURN area;
END;
This is the same function which we created in PL/SQL tutorial 39. Thus for detailed overview of this code you can refer to the tutorial here.
Step 2: Write the Dynamic SQL program.
SET SERVEROUTPUT ON;
DECLARE
plsql_blk VARCHAR2 (500);
BEGIN
plsql_blk :='
CREATE OR REPLACE FUNCTION circle_area (radius NUMBER)
RETURN NUMBER IS
pi CONSTANT NUMBER(7,2) := 3.141;
area NUMBER(7,2);'||
‘BEGIN
area := pi * (radius * radius);
RETURN area;
END;';
EXECUTE IMMEDIATE plsql_blk;
END;
/
The above code is pretty much the same except from the statement 1 of execution section. In the earlier code in statement 1 we assigned the anonymous block to the variable plsql_blk. While here in this code we have assigned the named PL/SQL block which is a function.
Step 3: Execute your Dynamic SQL program
Upon execution this code will create a PL/SQL function which accepts a single argument and returns the area of the circle.
That is how we use dynamic SQL for creating an anonymous as well as named PL/SQL block in Oracle Database. Please do make sure to share this post on your social media. Thanks and have a great day!
