Thursday, May 14, 2009

PLSQL Bind variable should be renamed.

I have something against the so called "Bind Variable" in PLSQL. To show what bind variable means, below is an example.

Slow SQL statement:
Execute immediate 'Delete from employee where id = ' || x;

Bind variable SQL statement which improve performance:
Execute immediate 'Delete from employee where id = :x' using x;

Ok, so far so good. In general sense, Bind Variable means binding a variable in sql statement to a value. In our second example, it shows what I have just stated.

Now the problem. What if i change the statement that it allow multiple table such as example below.

Bind variable for table
Execute immediate 'Delete from :table where id = :x' using tablename, x;

The above statement failed with error table or view not found. :D

In conclusion, the Bind Variable feature only worked in where clause, so it shouldn't called Bind Variable. It should be called Bind Where Clause Variable instead. :P

PS: Solution for the multiple table variable bind.
Execute immediate 'Delete from '|| tablename ||' where id = :x' using x;