Oracle PL/SQL Programming, 5th 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 that statement 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.

So what can you do with dynamic SQL and dynamic PL/SQL?^[16] 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 may 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 realize they are doing so).

Softcode business rules and formulas

Rather than hardcoding business rules and formulas into your code, you can place that logic in tables. At runtime, you can generate and then execute the PL/SQL code needed to apply the rules.

Ever ...