From my experience, research and general knowledge, Dynamic SQL should be avoided as much as possible,
There are multiple reliable sources that say this, even Oracle’s revered top DBA, Tom Kyte, and Oracle’s own PL/SQL Evangelist and Oracle ACE, Steven Feuerstein has mentioned this multiple times in AskTOM and Oracle Blogs.
To list some of the disadvantages of Dynamic SQL (from research and my own inputs):
It cannot be detected by the Oracle Enterprise Manager to have bad statistics because it is not parsed before it is run. It’s basically invisible to the DB parser.
The query this is not being “recorded” in the pool, therefore, we cannot predict if this certain piece of code will perform badly or not, we cannot quantify its statistics.
There are great efficiencies to be gained by using static sql, as PLSQL will cacheopen cursors and reuse statements, even before they are run.
Dynamic SQL is not scalable. Static SQL is scalable.
Does not really have any big advantage over static SQL apart from being “flexible”, but the disadvantages greatly outweighs these advantages.
It’s actually not true that Dynamic SQL performs better, in fact: dynamic SQL is more complex than static SQL, it executes more slowly than static SQL. Especially when done wrong.
We should Re-use code whenever possible, but only when that is appropriate. And I believe we can re-use code without doing Dynamic SQL. Do we REALLY need that SQL to be dynamic?
Dynamic SQL is only really considered when we don’t know what parameters are used before runtime, something like in OBIEE, where the user will be the one to input the columns they want and need, then run the query.
I believe we can re-write these programs to use fixed, structured, and static queries instead.
Dynamic SQL is prone to SQL-Injection in General. I don’t know if there will be a time where we will encounter this issue, but it will be a huge mess if it does happen.
Personally, I find them extremely hard to debug, hard to read, and impractical because we only find out the actual query at runtime.
We cannot see the dependencies between programs because as I mentioned, they are invisible and only appear during runtime.
It will be hard to see which programs get impacted when we change a certain code, which in turn, will result in another bug, etc.
I can list down more Pros and Cons, but I believe these are
enough details to establish that the disadvantages of Dynamic SQL outweighs the
advantages.
I strongly urge that our dev teams start to write Static SQL
instead of Dynamic SQL.
To be edited further...