O'Reilly logo

Oracle Database 11gR2 Performance Tuning Cookbook by Ciro Fiorillo

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required