Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training,
learning paths, books, tutorials, and more.

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training,
learning paths, books, interactive tutorials, and more.