Use of Bind Variable

What is Variable ?

Is a storage or reusable place holder on memory when user can store specific type of value


What is  Bind Variable ?

This also called as dynamic parameters or bind variables—are an alternative way to pass data to the database. Instead of putting the values directly into the SQL statement, you just use a placeholder like ?   (  :bind_var or  @bind_var   )



Advantage of Bind Variable

• Re-usability
• Can be shared across multiple blocks
• Also called as Session variable as can be used through out your session

• Security
• Bind variables are the best way to prevent SQL injection.

• Performance
• Improves databases performance with an execution plan existing in cache which can reused an execution plan when executing the same statement multiple times. It saves effort in rebuilding the execution plan but works only if the SQL statement is exactly the same. If you put different values into the SQL statement, the database handles it like a different statement and recreates the execution plan.

Example : 1
******************************************************************
--Bind variables can be declared in your  script.

VARIABLE  bv_var  varchar2(30)
   
--Preface a bind variable with a colon to reference it
-- Assigning  value to bind variable in one block

BEGIN
  :bv_var := 'Dev';
END;
/
-- Using bind variable in another block
BEGIN
  dbms_output,put_line ('NAME :' ||:bv_var );
END;
/

******************************************************************

----------------  OUTPUT ------------------

NAME : Dev

****************************************************************

Example : 2

****************************************************************
-- Value of Bind variable can also be printed using "PRINT"

PRINT :bv_var

------Output ------------

   bv_var
----------
Dev
************************************************

<< BACK HOME >>