Bind Variables

[veen]

What is an Oracle Bind Variable , how they are used in database as well as from Middle tier apps. Advantages and disadvantages of Bind Variables.

Thank You

[jkoopmann]

basically a bind variable is a holding position for a value you want to introduce in a where clause for a SQL statement.

instead of in your code having something like:
EXECUTE IMMEDIATE 'SELECT FROM emp WHERE empno = ' || TO_CHAR(emp_id);

you can have
EXECUTE IMMEDIATE 'SELECT FROM emp WHERE empno = :num' USING emp_id;
where emp_id is a variable.

in the first example the select statement is hard-coded with the emp_id and is parsed everytime a new emp_id is given.

in the second example there is only one parse for the sql statement (if it hasn't been aged out of memory) and therefore the time to execute is quicker.

the only disadvantage to bind variables is that they can sometimes have a different access path then the hard-coded value sql statment which may cause problems.