Use Bind Variables to Help the Outline Check

David Clement

September 2004

Oracle versions 7-8

Dynamically building SQL in a host-language string, with the values of host-language variables pasted into the string, is a bad idea. Such code might look like this:

          strcpy (s, "select sysdate from dual where 1=%d", hostvar);

The problem is that, since the variable value forms part of the string, there is almost no chance of the outline check succeeding. The success of the outline check determines whether Oracle hard-parses a SQL statement, so this can have a significant effect on performance.

There are other reasons to use bind variables. For example, the optimizer may be able to perform bind variable peeking to determine likely values for the variables, improving its choice among execution plans.

So, what is a bind variable? It is a placeholder in a SQL statement for a host-language variable. Oracle's on-line Ask Tom column (http://asktom.oracle.com) provides the following example.

          exec sql begin declare section;
          VARCHAR x[20];
          VARCHAR eno[20];
          exec sql end declare section;
          strcpy (x.arr, "KING");
          x.len = strlen (x.arr);
          exec sql select empno into :eno from emp
          where ename = :x;

The two bind variables in the embedded SQL statement are in boldface above.