USING clause with execute immediate statement Applied On Multiple Bind Variables

In the last tutorial we saw how to execute a SQL query which has a Single Bind Variable with Execute Immediate statement. But chances of using multiple bind variables in a project are much higher. Thus in today’s PL/SQL tutorial you will be learning how to use multiple bind variables with Execute Immediate of Native dynamic SQL.

Why bind variables?

As I mentioned earlier, the two biggest advantages of bind variables are security against SQL injections and performance enhancement by reducing hard parsing. Because of these two the use of bind variables becomes inevitable for developing a robust application.

Before moving from this point onwards in this tutorial, I want you to go and check out the previous tutorial and learn how to use a bind variable with execute immediate, in case you haven’t already.

So, do we still need to apply USING clause?

Yes, USING clause is inescapable. If we want to use bind variables with dynamic SQL then we need to apply USING clause with Execute Immediate statement.  

Can you tell me again what exactly is the purpose of this Using Clause?

Unlike static SQL where user specifies the value for bind variables after the execution, in the dynamic SQL we need to mention the values beforehand for all the bind variables used in the SQL query which you want to run using Execute Immediate statement. In order to do that we need USING clause.

By applying Using clause we can list down all the values for every bind variable used in the SQL statement.

What is the syntax for using multiple bind variables in Execute Immediate Statement?

The syntax is pretty simple. Here, take a look:

EXECUTE IMMEDIATE sql_query 
 USING list-of-bind-variables;

In native dynamic SQL Using Clause is used in conjunction with Execute Immediate, thus the statement starts with the reserved phrase ‘EXECUTE IMMEDIATE’ followed by a valid SQL query. Then we have our ‘Using Clause’.

In order to apply Using Clause, we first need to write a reserved keyword ‘USING’ followed by values for bind variables (a.k.a. bind arguments).

In case if you have used multiple bind variables in your SQL query then write the values for each of those right after the keyword USING and make sure to separate them from each other using coma.

So now let’s do an example.

Example demonstrating how to use multiple bind variables with Execute Immediate of Native Dynamic SQL?

For the demonstration of multiple bind variables we will use UPDATE DML with Execute Immediate. That way we can learn, how to use multiple bind variables applying using clause as well as how to use UPDATE DML with Execute Immediate. So here we will see how to update values of a table using bind variables in Native Dynamic SQL.

Step 1: Prepare a table.

If you already have a table which has some data into it then you don’t need to follow step 1 and 2, simply jump onto step 3.

CREATE TABLE stu_info(
    student_name    VARCHAR2(20)
);

The above Create Table DDL will create a table with the name stu_info with one column student_name which can store data of VARCHAR 2 datatype.

Step 2: Insert some data.

In order to update the data your table needs to have some records into it. As we just created a table thus we need to insert some records into it.

INSERT INTO stu_info (student_name) VALUES ('Tony');
INSERT INTO stu_info (student_name) VALUES ('Banner');
INSERT INTO stu_info (student_name) VALUES ('Leo'); 
INSERT INTO stu_info (student_name) VALUES ('Rocket');
INSERT INTO stu_info (student_name) VALUES ('Steve');

Here we have five INSERT DML. On execution these DML will insert five rows of data into your table stu_info.

You can confirm the insertion of data by writing the simple SELECT statement.

SELECT student_name FROM stu_info;

The output of this statement must be the five rows of data and will look something like this.

We could have done both the above steps using native dynamic SQL. But this way we can keep the concept simple and easy to understand.

multiple bind variable with execute immediate by manish sharma

Step 3: Write the dynamic SQL program.

Before you start writing the PL/SQL program let’s first prepare the UPDATE DML which you want to execute dynamically. Although it is not required but still it’s a good practice as this way we get more control over the code while keeping it neat and clean.

UPDATE stu_info SET student_name = :new_name 
    	WHERE student_name = :old_name

This is a very simple update DML. Using this we are updating the values of student_name column of our table stu_info. As we have used the WHERE clause thus only those values of the student_name column will get updated that fulfill the condition of the clause.

Info:
Always make sure not to end the SQL statement which you want to run with EXECUTE IMMEDIATE with a semi colon otherwise you will end up getting an error.

Also, In this DML instead of directly hard coding the values we have used two bind variables “:new_name” and “:old_name”.

Applying USING Clause Of Execute Immediate Statement

Next we will write a PL/SQL program where we will see how to take care of these bind variables by applying Using Clause of execute immediate statement.

Set Serveroutput On;
Declare
    Sql_Smt Varchar2(150);
Begin
    Sql_Smt := 'UPDATE stu_info SET student_name = :new_name 
    WHERE student_name = :old_name ';
    Execute Immediate Sql_Smt Using 'Strange','Leo';   
End;
/

This PL/SQL program is pretty much the same as that we have seen previously. In the declaration section we have declared a variable “sql_smt” of VARCHAR2 datatype. We will be using it for storing our SQL statement which is an Update DML that we just prepared.

We have also set the data width of this variable to 150 which I guess should be enough for holding our update dml. If in case it’s not then we could always modify it later accordingly.

What could be the Error?
How about you set the data width of your variable to 10 in your program and tweet me your result, I mean tweet me the error.

In the execution section we have only two statements. The first one is the assignment statement where we are storing our Update DML statement into the variable SQL_SMT. The second one is the Dynamic SQL statement. In this we are executing the SQL query dynamically using Execute Immediate.

As our SQL query consists of two bind variables thus it becomes mandatory for us to list down the values for those bind variables beforehand. In order to do that we apply USING clause. As you can see in this code by applying ‘USING’ clause we have specified the values “Strange” and “Leo” that will substitute themselves in place of bind variables on execution.

Rules for using bind variables with Execute Immediate of Native Dynamic SQL.

  • In native dynamic SQL we need to list down the values for all the bind variables used in the SQL query beforehand.
  • You cannot use schema object names such as table name as bind argument in native dynamic SQL.
  • Data type of bind argument should either match or be compatible with the datatype of its corresponding context. For example the column student_name can hold values either of Varchar2 datatype or those compatible with this datatype. Here the value “Strange” is of character datatype which is compatible to varchar2 datatype.
  • The order of values in the list of USING clause must match with that of the bind variables in the query. This means the first value in the list of USING clause will get assigned to the first bind variable in the query and so on.

Do make sure to watch the video tutorial!

There we have tried to find out the answers in an unconventional way using practical demonstration to the questions like:

  • What will happen if I don’t specify the value for all the bind variables and
  • Why didn’t we use bind variable for the table name?

So, in this tutorial we learnt how to use multiple bind variables by applying Using clause of Execute Immediate statement in Native Dynamic SQL. Hope you enjoyed reading. Thanks and have a good day.

RELATED ARTICLESMORE FROM AUTHOR