In general bind variables are Oracle variables which holds data and resides in separate memory area for performance benefits and faster access.
SQL> variable i number;
SQL> exec :i := 1;
PL/SQL procedure successfully completed.
SQL> print :i
Here :i act as a bind variable
execute immediate ‘select deptname from dept d where d.deptno = :x’
Here 😡 is a bind variable. 😡 got assigned value 1 at run-time.
All cursor variables are bind variables
cursor c1(cv_dno integer)
select count(*) from emp e where e.deptno = cv_dno;
fetch c1 into n_cnt;
Here cv_dno is a bind variable.
All PL/SQL variables are bind variables.
CREATE OR REPLACE PROCEDURE
P_INSERT_DEPT(p_deptno integer,p_deptname varchar2,p_location varchar2)
insert into dept values(p_deptno,p_deptname,p_location);
Here p_deptno,p_deptname and p_location are bind variables. So it is a nice idea to wrap every thing using PL/SQL variables.
Misconception about Bind Variables
1. Any variable used with : (colon) is a bind variable – not true always
2. Bind variables only referenced from SQL Plus – not true
3. Static SQL in PL/SQL block must be re-written with EXECUTE IMMEDIATE to utilize bind variables ( same as example 2) – Not at all true
Conclusion :- It is true that bind variables improves the performance, doesn’t mean that you always need to use it. But keep it in mind that for large queries bind variables will save a lot of time.