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? Here are just a few ideas:
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!
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.
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 ...