Bind variables Oracle

In general bind variables are Oracle variables which holds data and resides in separate memory area for performance benefits and faster access.

Example 1

SQL> variable i number;
SQL> exec :i := 1;

PL/SQL procedure successfully completed.

SQL> print :i

I
———-
1

Here :i act as a bind variable

Example 2

declare
v_dname varchar2(30);
begin
execute immediate ‘select deptname from dept d where d.deptno = :x’
into v_dname
using 1;
dbms_output.put_line(v_dname);
end;

Here ūüė° is a bind variable. ūüė° got assigned value 1 at¬†run-time.

Example 3

All cursor variables are bind variables

declare
cursor c1(cv_dno integer)
is
select count(*) from emp e where e.deptno = cv_dno;
n_cnt integer;
begin
open c1(1);
fetch c1 into n_cnt;
close c1;
dbms_output.put_line(‘n_cnt–>’|| n_cnt);
end;

Here cv_dno is a bind variable.

Example 4

All PL/SQL variables are bind variables.

CREATE OR REPLACE PROCEDURE
P_INSERT_DEPT(p_deptno integer,p_deptname varchar2,p_location varchar2)
AS
BEGIN
insert into dept values(p_deptno,p_deptname,p_location);
commit;
END P_INSERT_DEPT;

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.

Advertisements

Author: sqlandplsql

To learn more about the Oracle, SQL, PL SQL, Performance Tuning, Database Modeling, Ubuntu, MySQL etc .Suggestions, comments, feedbacks and referrals are highly appreciated.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s