James Wiseman

Dynamic SQL is, by its nature, very flexible and often very useful. However, it is this flexibility that can be both a blessing and a curse.

The offical Oracle recommendations on Static vs Dynamic SQL state that, in practice, “static SQL will meet nearly all your programming needs.”

They go on to give a number of conditions under which they deem it acceptable for you to use dynamic SQL. They suggest it is used when one or more of the following items is unknown at precompile time:

• text of the SQL statement (commands, clauses, and so on)
• the number of host variables
• the datatypes of host variables
• references to database objects such as columns, indexes, sequences, tables, usernames, and views

Although this may be true, I would prefer to add the caveat “Only if you absolutely cannot find a way to do it in Static SQL”

They also state:

However, some dynamic queries require complex coding, the use of special data structures, and more runtime processing. While you might not notice the added processing time, you might find the coding difficult unless you fully understand dynamic SQL concepts and methods.

So rather than have a long and tedious discussion as to the relative merits, lets just list the advantages and disadvantages in a series of bullet points.

Advantages

• It gives flexibility and scalability
• It can reduce the number of lines of code written

Disadvantages

• It can become very complex and difficult to read. Think about quotes embedded in quotes, and other such things.
• It can have a detrimental effect on code stability. Some Dynamic SQL errors will not be known until run time. (An example of this is where you reference a non-existent table)
• Dynamic SQL code is harder to test than the equivalent static SQL. It may also be impossible to test for every possible circumstance that your Dynamic SQL will encounter, thus introducing inherent risk.
• It will be more difficult to conduct an effective impact analysis on Dynamic SQL in your code-base.
• SQL injection and misuse – Dynamic SQL is more prone to misuse, and is invariably less safe than static SQL
• The queries code within Dynamic SQL is not subject to a query plan, and as such optimisations may be missed. As such, it can be slower than the equivalent static SQL
• As the SQL query is not known until runtime, it can be harder to performance-tune SQL Dynamic code (for example, determining the indexes that might be required on a table)

So there we are. Take that and pin in on a wall somewhere