When Should We Use Dynamic SQL?

A comparison between static SQL vs dynamic SQL shows us pretty clearly that, if there exists a choice, we are better off choosing static SQL.

So, when should we use dynamic SQL?

Oracle documentation tells us that we need dynamic SQL to run:

• SQL whose text is unknown at compile time

  For example, a SELECT statement that includes an identifier that is unknown at compile time (such as a table name) or a WHERE clause in which the number of subclauses is unknown at compile time.
  

• SQL that is not supported as static SQL

Here are a few use cases where it makes sense to use dynamic SQL.

1. Programs than run DDL statements

DBA housekeeping scripts are a good place for dynamic SQL. This is because DDL statements (such as CREATE, DROP) can be executed in PL/SQL using only dynamic (not static) SQL.

For example, a periodically-run PL/SQL procedure to find and truncate all staging tables in the database (assuming a convention of naming such tables with suffix ‘STAGING’):

 -- Procedure using dynamic SQL to truncate staging tables
create or replace procedure purge_staging_tables
is
begin
for r in (select table_name 
          from all_tables 
          where table_name like '%STAGING') loop

      execute immediate 'truncate table ' || r.table_name;

   end loop;
end purge_staging_tables;

Looking through the data dictionary and issuing DDL statements needs the power of dynamic SQL.

2. Programs that run SCL statements

As with DDL statements, SCL statements (such as  ALTER SESSION, SET ROLE) too can be executed in PL/SQL using only dynamic (not static) SQL.

Such as a PL/SQL procedure to enable session parameters:

-- Procedure using dynamic SQL to alter session
create or replace procedure enable_session_params
is
begin

  execute immediate 'alter session set sql_trace true';
  
end;

3. Ad-hoc query systems

Ad-hoc queries are those constructed on-the-fly via interfaces such as BI reporting tools, complex search screens, etc. Such queries tend to have a number of variables (select list, table names, where clause) unknown at compile time — this calls for dynamic SQL.

Note that when user input is involved in dynamic SQL, stronger safeguards against SQL injection are called for.

Related FAQ:
Oracle provides two ways of executing dynamic SQL –

In general, native dynamic SQL is easier to use and more compact, while DBMS_SQL provides more flexibility (e.g. supports scenarios when you do not even know the number or types of input/output columns until runtime).

Both ways are described in the Oracle documentation with recommendations. Choose the approach that best fits the nature of the ad-hoc query.

Postscript: Before you go dynamic, think again

Even in case of "SQL whose text is unknown at compile time", don’t be too quick to go dynamic: with a little thought, you can — and probably should — refactor your solution to work with static SQL. See Common Cases when to (Not) Use Dynamic SQL — this article’s written for SQL Server but a lot of it holds good for Oracle too.