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, we cannot use static SQL inside PL/SQL code. So, if we want to grant the RESOURCE role to the user SH, we have to do something similar to the following:

BEGIN
  EXECUTE IMMEDIATE 'GRANT RESOURCE TO SH'
END;

To search the EMPLOYEES table...