Mythbusting Oracle Dynamic SQL

The biggest challenge in learning Dynamic SQL is to get past your initial fear of this feature. Of course, with any advanced technology comes risk with its misuse. In the case of Dynamic SQL, the chances of encountering security or performance issues often outweigh the potential benefits. With proper understanding of the underlying mechanisms, you can avoid these issues and mitigate any risks.

Dynamic SQL is constantly evolving from version to version. You need to recognize all the subtle nuances applicable to your environment (and not anyone else’s) because eventually these details could save real projects.

The important points to keep in mind regarding Dynamic SQL are as follows:

• Dynamic SQL is about managing the unknown. The less information you have at compilation time, the more benefits you will obtain from adjusting your code on the fly.
• You can easily protect yourself from code injections if you follow security best practices. The most important one is to always use bind variables.
• Whenever you cannot avoid string concatenation, you should use DBMS_ASSERT.
• When you compare solutions that involve Dynamic SQL to solutions that use regular SQL and PL/SQL, you need to be aware of the system impact. Depending upon the available resources under different circumstances, the performance best practices may change.
•  Starting with Oracle Database 11g, you can use CLOB as input for EXECUTE IMMEDIATE, which means that DBMS_SQL is required only when you need a granular execution control or when IN/OUT parameters are unknown.
•  Starting with Oracle Database 11g, you can transform REF CURSOR and DBMS_SQL cursors.
•  Dynamic SQL is tightly integrated with the PL/SQL Function Result Cache feature.
•  In Oracle Database 11g and 12c, Dynamic SQL supports more and more complex datatypes (user-defined  types, PL/SQL-only types, collections), but restrictions still exist.