Native dynamic SQL or NDS is helping developers by providing flexibility, improving performance and simplifying programming syntaxes. Today in this tutorial we are going to learn how to write a Dynamic SQL query in Oracle Database using Execute Immediate Statement.
So what is an Execute Immediate Statement in Oracle Database?
Using Execute Immediate we can parse and execute any SQL statement or a PL/SQL block dynamically in Oracle Database. And by dynamically I mean at runtime.
Execute immediate takes only one argument which can either be a SQL statement or a PL/SQL block. Compiler treats the arguments of the Execute Immediate statement as the string of VARCHAR2 data types. So do make sure to enclose your SQL query or PL/SQL block into the pair of single quotes ( ‘ ’ ).
Can we use any SQL statement with Execute Immediate?
Any SQL statement or PL/SQL block which returns single row of results can be used with Execute Immediate. If your statement returns more than one row of results then there are other ways which we will discuss in future tutorials.
What is the syntax of Execute Immediate Statement?
Syntax of Execute Immediate statement is pretty simple. Let’s take a look at it.
EXECUTE IMMEDIATE dynamic_query [INTO user_defined_variable-1, user_defined_variable-2…] [USING bind_argument-1, bind_argument-2…] [RETURNING|RETURN-INTO clause];
Execute Immediate: Execute Immediate is a reserved phrase.
Dynamic_query: Followed by the reserved phrase we have to write our dynamic query. This query could be a SQL statement or a PL/SQL block. Compiler treats the dynamic query as a string of VARCHAR2 data type thus you have to make sure that you enclose your query into a pair of single quotes.
INTO clause: Using INTO clause we specify the list of the user defined variables which will hold the values returned by dynamic SELECT statement. It is very similar to the SELECT-INTO statement. Also it is an optional clause, so if you don’t require it then you can omit it.
USING clause: In case you have used a bind variable in your dynamic query then this clause will enable you to specify the values for that bind variable which will get substituted accordingly during the runtime. It is again an Optional clause.
RETURNING or RETURN INTO clause: Return into clause is opposite to the USING clause. Where in using clause we supply the values to the dynamic query here in RETURNING INTO clause we get the values returned by the dynamic query and store them into the specified list of bind arguments. It is again an Optional Clause.
You can also watch the detailed explanation of the above syntax in the corresponding video here.
Example of Execute Immediate statement.
Doing a demonstration of execute immediate using all the above mentioned clauses will increase the complexity and make the example difficult to understand. Which is exactly the opposite of what we want.
Thus to keep the concept simple and easy to learn we will do the example of Execute Immediate using the first clause which is INTO.
Execute Immediate with INTO clause.
SET SERVEROUTPUT ON; DECLARE sql_qry VARCHAR2 (150); emp_tot NUMBER(3); BEGIN sql_qry:= ‘SELECT count (*) FROM employees'; EXECUTE IMMEDIATE sql_qry INTO emp_tot; DBMS_OUTPUT.PUT_LINE ('Total employees are: '||emp_tot); END; /
Here is a very simple example demonstrating how to use execute immediate with INTO clause in Oracle Database. Let’s see what we did here.
In the declaration section we have declared two variables. The first variable is sql_qry of VARCHAR 2 type. We will use this variable to store the SELECT statement which we want to execute with our EXECUTE IMMEDIATE statement. As this variable is going to hold a DML statement thus we have to make sure that it’s got a sufficient data width.
The second one is user defined variable emp_tot. We will be using this variable with INTO clause to hold the data returned by our SELECT statement.
In this section we only have three statements. These are:
Statement 1: In the first statement we are assigning a valid SQL query to the variable sql_qry.
Statement 2: The second statement is the EXECUTE IMMEDITAE – INTO statement. In this statement right after writing the reserved phrase execute immediate we write the name of the variable sql_qry. The same variable into which we store the SELECT statement.
On execution the runtime engine will replace this variable with the content it is holding, which in our case is a SELECT statement. If there is no error then the runtime engine will execute the underlying SELECT statement and return its result if there is any.
Our SELECT statement will return a value which is the total number of rows of employees table. Using INTO clause of EXECUTE IMMEDIATE statement we will store that returning value into the variable emp_tot.
Statement 3: The third statement is an output statement using which we are displaying the value of the emp_tot variable back to the user.
In case of DML transaction an explicit commit is required as Execute Immediate will not automatically commit a DML transaction.
An alternative way of writing this execution section is:
BEGIN EXECUTE IMMEDIATE 'SELECT count (*) FROM employees' INTO emp_tot; DBMS_OUTPUT.PUT_LINE ('Total employees are: '||emp_tot); END; /
If you want, you can skip the first statement completely just by writing the SQL query directly after the reserved phrase EXECUTE IMMEDIATE.
Personally I like the former way where we used the variable for storing the query and later used that variable with Execute Immediate because that makes our code look neat and clean as well as helps us in keeping a track on our query just in case if we ever want to change or modify it.
If you find learning through watching videos much convenient then you can watch the video on my YouTube channel to learn about Execute Immediate with INTO clause.
So far in this tutorial we learnt what the Execute Immediate Statement is and how to use the same for executing a SQL query dynamically using INTO clause in Oracle Database. I think that’s enough for this tutorial. Let’s keep it simple by not stretching it further.
Do make sure to Subscribe to my YouTube channel for more Interesting tutorials.
Thanks and have a great day!