The Grumpy DBA Blogs . . .

TOD – The Ties that Bind (Variables) August 25, 2008

Tuning SQL is not exactly my favorite thing, and I have seen times when a SQL query runs fine with hard coded values, but poorly with bind variables in the application. There is a simple way to test bind variables in a SQL statement if needed.

Declare the variable, the bind it to a value using PL/SQL and insert into your script:

variable a1 varchar2(10);
SQL> variable a1 varchar2(10);
SQL> variable b1 varchar2(10);
SQL> variable c1 number;
SQL>
SQL> exec :a1 := 'Jay'

PL/SQL procedure successfully completed.

SQL> exec :b1 := 'DBA'

PL/SQL procedure successfully completed.

SQL> exec :c1 := 21

PL/SQL procedure successfully completed.

SQL> select last_name, DOB, favorite_marshmellow
  2  from emp
  3  where
  4  first_name = :a1 and
  5  job_title = :b1 and
  6  age > :c1;
/