Bind, don’t concatenate, to optimize performance and simplify dynamic string construction ↗

Steven Feuerstein gives us a great and simple example of how to use bind variables to avoid concatenation of variable values into dynamic SQL strings and, at the same time, improve performance by order of magnitude:

When you use bind variables, you greatly simplify the task of writing the dynamic SQL string. You don’t have to write all that concatenating code and you don’t have to perform datatype conversions. The USING clause automatically performs native binding of the appropriate types.

He also notes:

You can bind only variable values. You can’t bind in the names of tables or columns, nor can you bind in parts of a SQL statement structure, such as the entire WHERE clause. In these cases, you must use concatenation.

Filed in Oracle on 30 May 12 | Tags: performance, pl/sql

---