execute immediate with into clause in oracle database by manish sharma

Execute Immediate With INTO Clause

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. It 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. Therefore 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. Furthermore if your statement returns more than one row of results then there are other ways. Additionally these 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];  

Where:

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. Moreover the compiler treats the dynamic query as a string of VARCHAR2 data type. Therefore 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. In addition these 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. These in turn will get substituted accordingly during the runtime. Again, it is an Optional clause.

RETURNING or RETURN INTO clause: Return into clause is opposite to the USING clause. Whereas 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. Again it is 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.

Declaration Section

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.

Execution Section

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 IMMEDIATE – 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. Thereafter return its result if there is any.

Meanwhile 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.

Info:
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 write the SQL query directly after the reserved phrase EXECUTE IMMEDIATE.

Please watch the video on my YouTube channel to learn what is the proper way of writing the query for Execute Immediate.

What You Guys Think?

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. Moreover it helps us keep a track on our query just in case if we ever want to change or modify it.

What do you guys think? Which way of writing Execute Immediate you like the best? The former one or the latter one. Tell me your opinions on my Facebook page or on my Twitter. 

If you find learning through watching videos much convenient then you can watch the video on my YouTube channel. And 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!