Oracle PL/SQL Programming, 6th Edition

Chapter 16. Dynamic SQL and Dynamic PL/SQL

Dynamic SQL refers to SQL statements that are constructed and executed at runtime. Dynamic is the opposite of static. Static SQL refers to SQL statements that are fully specified, or fixed, at the time the code containing those statements is compiled. Dynamic PL/SQL refers to entire PL/SQL blocks of code that are constructed dynamically, then compiled and executed.

Time for a confession: I have had more fun writing dynamic SQL and dynamic PL/SQL programs than just about anything else I have ever done with the PL/SQL language. By constructing and executing dynamically, you gain a tremendous amount of flexibility. You can also build extremely generic and widely useful reusable code. That said, you should only use dynamic SQL when it is needed; static SQL is always preferred, since when you make your code dynamic, it is more complicated, harder to debug and test, possibly slower, and more difficult to maintain.

So what can you do with dynamic SQL and dynamic PL/SQL?^[17] Here are just a few ideas:

Execute DDL statements

You can only execute queries and DML statements with static SQL inside PL/SQL. What if you want to create a table or drop an index? Time for dynamic SQL!

Support ad hoc query and update requirements of web-based applications

A common requirement of Internet applications is that users be able to specify which columns they want to see and vary the order in which they see the data (of course, users don’t necessarily realize they are ...