Why Use Dynamic SQL?
Dynamic SQL and static SQL both have advantages and disadvantages. The full text of static SQL statements is known at compilation, which provides the following benefits: • Successful compilation verifies that the SQL statements reference valid database objects and that the necessary privileges are in place to access the objects. • Performance of static SQL is generally better than dynamic SQL. Despite these advantages, static SQL has limitations that can be overcome with dynamic SQL, as in the following cases: • You do not know the full text of the SQL statements that must be executed in a PL/SQL procedure. These SQL statements may depend on user input or on processing work performed by the program. • You want to execute DDL statements and other SQL statements that are not supported in purely static SQL programs. • You want to write a program that can handle changes in data definitions without the need to recompile. Dynamic SQL is more flexible than static SQL because it enables you to
