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

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.
Consider the following example.
A table is created...
create table bind_ex (
   col_1 number,
   col_2 varchar2(10)
);
... and filled with some values:
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).
In PL/SQL, execute immediate can be used for bind variables:
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;
/
Again checking v$sql:
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.

Links

Diagnostic event 10046 (level 4 and 12).