Insert DML With Bind Variable: Why?

Let’s cut to the chase! The two biggest advantages of bind variables are security against SQL injections and performance enhancement by reducing hard parsing. Because of these advantages, the use of bind variables in your code is not just suggested but also recommended.

Will it not be an icing on the cake if we could figure out a way to use Bind Variables with Native Dynamic SQL? If you are wondering so then my dear friends you are at the right place as we are going to do exactly that in this blog.

What is a bind variable?

We can define bind variable as the variable that we create in SQL*Plus and then reference in PL/SQL. Furthermore we can declare a Bind variable anywhere in the host environment. Therefore bind variables are also known as host variable.

You can read more about bind variables in PL/SQL tutorial 6 here.

How to use a bind variable with Native Dynamic SQL?

In order to use bind variable in dynamic SQL we need to take the help of “Using Clause” of Execute Immediate statement.

What is this Using Clause of Execute Immediate statement?

In static SQL we had to specify the value for bind variable after execution of the PL/SQL program. While in Dynamic SQL we have to mention the values beforehand for all the bind variables. The same ones that are used in the SQL statement which we desire to execute dynamically using Execute Immediate.

Therefore, to specify the values for all the bind variables we take the help of USING clause of Execute Immediate Statement. By applying ‘USING’ clause we list down all the values for every bind variable used in the SQL statement that we desire to execute dynamically.

Now that we have learnt what are bind variables and Using Clause of Execute Immediate statement let’s do an example.

Example of Execute Immediate with Using Clause

For the demonstration we will insert the data into a table. And for that we will use the Execute Immediate statement. Also, to enhance the security and the performance we will be using bind variable with the Insert DML.

In order to do that we will first need a table into which we can insert the data. Therefore, let’s quickly create one. And like always I will be keeping the code as simple as possible for easy understanding of the concept.

Step 1: Create a table

CREATE TABLE stu_info(
    student_name    VARCHAR2 (20)
);

Step 2: Prepare the DML statement

It is a good programming practice to prepare your SQL statement, which you desire to execute using execute immediate, beforehand. This way you will reduce the chances of syntax errors.

Restriction for writing DML for Execute Immediate will remain the same as that for DDL statement. Similar to DDL statement DML statement should not be ended with a semi colon. Therefore, keeping that in mind let’s write the INSERT statement.

INSERT INTO stu_info (student_name) VALUES (:stu_name)

In standard INSERT DML we usually write the data into the parenthesis of values clause. Furthermore on execution, this data will get inserted into the corresponding column. But as you can see, here we have used a bind variable “:stu_name” instead of hard coding any specific value for inserting into the table.

Step 3: Write the PL/SQL code

The last step in the cycle is to run INSERT DML with bind variable by applying USING clause of Execute Immediate Statement of Native Dynamic SQL.

SET SERVEROUTPUT ON;
DECLARE
    sql_smt VARCHAR2 (150);
BEGIN
    sql_smt :=  'INSERT INTO stu_info (student_name) VALUES (:stu_name)';
    EXECUTE IMMEDIATE sql_smt USING 'Steve';
END;
/

Let’s understand what will happen on execution of this PL/SQL block. When you will execute this program, the PL/SQL engine will substitute this value ‘Steve’ in place of bind variable :stu_name and then execute the INSERT DML which will insert this value into the mentioned table.

Furthermore there are a few things which you need to take care of while writing the above PL/SQL program at your end.

  1. Make sure to enclose the INSERT DML or Any SQL statement which you desire to run using Execute Immediate into a pair a single quotes. Also, remember to end the assignment statement (line 5) as well as your Execute Immediate statement with a semi colon.
  2. The value you are assigning to your bind variable through the USING clause must either be the same or compatible with the data type of the column of the table into which you are inserting it. For example in our case we are inserting the name ‘Steve’ which is a character string into the column student_name of stu_info table which is of Varchar2 datatype. Character data type is very compatible with Varchar2 datatype.

Through this program we learnt two very important things.

  1. How to use an INSERT DML with Execute Immediate of Dynamic SQL and
  2. How to apply USING clause for assigning values to the bind variable used in the SQL query which we desire to run dynamically.

In addition, do make sure to check the video as there I have discussed how to deal with “ORA-01008: Not All Variables Bound” Error. I bet you don’t want to miss the solution to this error as it is very important from oracle database certification perspective.

Also, stay tuned as in the next tutorial we will learn how to deal with multiple bind variables by applying Using clause of execute immediate in Oracle Database.

Hope you learnt something valuable from this blog. Meanwhile, please do share it with your friends on your Facebook, twitter, WhatsApp or any medium you find convenient.  Also, subscribe to my YouTube channel for more quick and interesting tutorials. Thanks and have a great day!

1 COMMENT