Aarti

• Jan 15th, 2014

 

Bind variable is session specific variable. It is declared using variable keyword. Bind variable cant be used in named block. It cant have default value.

pradipta

 Profile Answers by pradipta 

• Feb 8th, 2014

 

The way to get Oracle to reuse the execution plans for "the statements having different hard coded value in where clause"
there should use bind variables.
Bind variables are «substitution» variables that are used in place of literals (such as 674, 234, 332) at where clause
and that have the effect of sending exactly the same SQL to Oracle every time the query is executed.
For example, in our application, we would just submit

SELECT fname, lname, pcode FROM cust WHERE id = :cust_no;

and this time we would be able to reuse the execution plan every time,
reducing the latch activity in the SGA, and therefore the total CPU activity,
which has the effect of allowing our application to scale up to many users on a large dataset.

Actually the bind stands for bind other application value to the back end oracle SQL code.