Oracle PL/SQL Programming, Third Edition

Chapter 15. 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 fixed at the time a program 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?^[19] 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!

Build back-ends for web-based applications

These might allow users to specify which columns they want to see and vary the order in which they see the data. In other words, you can support full ad hoc querying and updating applications.

Write a generic string parsing engine

Such a parsing engine might accept a delimited list and deposit the elements of that list into your collection.

Ever since Oracle Version 7.1, we PL/SQL developers have been able to use the built-in DBMS_SQL package to execute dynamic SQL. In Oracle8i, we were given a second option ...