Posts Tagged bind variables

TOD – The Ties that Bind (Variables)

Posted by Jay Caviness in Tip 'o the day, general on August 25th, 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;
/

, ,

No Comments