Oracle Database 11gR2 Performance Tuning Cookbook

Avoiding dynamic SQL

The title of this recipe should be extended to say "… when you can do your stuff without using it". In this recipe, we will see when and how to use dynamic SQL.

Dynamic SQL is the only choice when:

• We want to execute DDL statements in our application.
• We have to code different queries depending on user input, for example, a search form with different search criteria that the user can choose from. This leads to different predicates in the WHERE clause.
• We want to code generic procedures, which can act on any table, for example, a generic "print" procedure, which shows the content of a table in a certain format.

For each of these situations, there are drawbacks to be taken care of.

How to do it...

To execute DDL statements in our application, ...