Dynamic SQL is an enhancement over static SQL. It enables writing SQL queries at run-time. It comes into picture when we need to customize SQL queries during execution.
Few common cases where Dynamic SQL can be utilized:
•If database objects do not exist at compile time, we can use dynamic SQL to reference them.
•Certain DDL (Data Definition Language) statements and SCL (Session Control Language) Statements are not supported by Static SQL in PL/SQL. These statements can be executed by via dynamic SQL programming.
•Dynamic SQL can be used for executing Dynamic PL/SQL block. By using EXECUTE IMMEDIATE clause, PL/SQL calls can be determined at runtime.