Chapter 16.  Dynamic SQL and Dynamic PL/SQL

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?[1] Here are just a few ideas: [1] For the remainder of this chapter, any reference to "dynamic SQL" also includes dynamic PL/SQL blocks, unless otherwise stated. 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 since Oracle7 Database Release 1, we PL/SQL developers have been able to use the built-in DBMS_SQL package to execute dynamic SQL. In Oracle8i Database, we were given a second option for executing dynamically constructed SQL statements : native dynamic SQL (NDS ). NDS is a native part of the PL/SQL language; it is much easier to use than DBMS_SQL and, for many applications, it will execute more efficiently. There are still requirements for which DBMS_SQL is a better fit; they are described at the end of this chapter. For almost every situation you face, however, NDS will be the preferred implementation approach.