Bind variables [Oracle]

If an SQL statement were thought of as a function (in the sense of programming languages) that manipulates data DML, then bind variables can be thought of as the input parameters to that function.

create table bind_ex (
   col_1 number,
   col_2 varchar2(10)
);

insert into bind_ex values (1, 'one'  );
insert into bind_ex values (2, 'two'  );
insert into bind_ex values (3, 'three');

Although the three insert statements do conceptually the same thing, they are not the same statement. This can be verified by examing v$sql:

set linesize 100
select substr(sql_text, 1, 100) from v$sql where lower(sql_text) like '% bind_ex %';

select substr(sql_text, 1, 100) from v$sql where lower(sql_text) like '% bind_ex %'
insert into bind_ex values (2, 'two'  )
insert into bind_ex values (3, 'three')
insert into bind_ex values (1, 'one'  )

The line returned is the statement to retrieve the other three. Since these three other statements are different, Oracle had to analyse them first in order to execute them. This is a costly operation and referred to as hard parse.

Since it is (sometimes) desirable to prevent such hard parses, bind variables can be used. In an SQL statements, bind variables are indicated by a colon (:) followed by a name or a number. So, these insert statements rewritten with bind variables will then look like: insert into bind_ex values(:bind_var_1, :bind_var_2).

declare
  stmt constant varchar2(52) := 'insert into bind_ex values(:bind_var_1, :bind_var_2)';
begin
  execute immediate stmt using 4, 'four';
  execute immediate stmt using 5, 'five';
  execute immediate stmt using 6, 'six' ;
end;
/

select substr(sql_text, 1, 100) from v$sql where lower(sql_text) like '% bind_ex %';

select substr(sql_text, 1, 100) from v$sql where lower(sql_text) like '% bind_ex %'
insert into bind_ex values (2, 'two'  )
insert into bind_ex values (3, 'three')
insert into bind_ex values (1, 'one'  )
insert into bind_ex values(:bind_var_1, :bind_var_2)
declare   stmt constant varchar2(52) := 'insert into bind_ex values(:bind_var_1, :bind_var_2)'; begi

The statment returns two new Statements: the PL/SQL block and the one that was (three times) executed within the block.


	René Nyffenegger's collection of things on the web
	René Nyffenegger on Oracle - Most wanted - Feedback - Follow @renenyffenegger

Bind variables [Oracle]

Links