Static SQL vs Dynamic SQL: Which to use?

Some questions do not have definitive answers. "Is a full table scan bad? Should this design be denormalized? Will partitioning this table help?" The answers vary widely depending on the specifics on the problem.

Fortunately, "Should I use static SQL or dynamic SQL?" is not one of those questions.

You can come to a decision quickly  if you follow this rule of thumb:

Consider dynamic SQL only if it cannot be done in static SQL.

Why?

Here are a few reasons why, if there exists a choice, you should go for static SQL.

1. Static SQL provides compile time checking. Dynamic SQL does not.

Let’s say we write a PL/SQL stored procedure containing a buggy SQL (e.g. invalid column name in SELECT) — static SQL in the first example, dynamic SQL in the second. Compile the procedure.

With static SQL:

SQL> -- PL/SQL procedure with static SQL
SQL> -- containing an error
SQL> create or replace procedure
  2             column_wrong_static
  3  as
  4     name varchar2(10);
  5
  6  begin
  7    -- Incorrect column name in SQL
  8
  9    select namewrong into name
 10    from emp
 11    where rownum = 1;
 12  end;
 13  /

Warning: Procedure created with compilation errors.

SQL> sho err
Errors for PROCEDURE COLUMN_WRONG_STATIC:

LINE/COL
--------
ERROR
---------------------------------------------------
9/3
PL/SQL: SQL Statement ignored

9/10
PL/SQL: ORA-00904: "NAMEWRONG": invalid identifier

With static SQL, you learn right at the time of compilation that there is something wrong with it. If the code refers to non-existent database objects or the privileges to access the objects are missing, the compiler gives you that information then and there.

With dynamic SQL:

SQL> -- PL/SQL procedure with dynamic SQL
SQL> -- containing an error
SQL> create or replace procedure
  2                   column_wrong_dynamic
  3  as
  4     name varchar2(10);
  5
  6  begin
  7    -- Incorrect column name in SQL
  8    execute immediate
  9    'select namewrong into name
 10    from emp
 11    where rownum = 1';
 12  end;
 13  /

Procedure created.

In the dynamic SQL version of the same query, no error was reported; the procedure got compiled successfully. Dynamic SQL has delayed the detection of the error, which means you will get an unpleasant surprise when you run the procedure.

SQL> exec column_wrong_dynamic;
BEGIN column_wrong_dynamic; END;

*
ERROR at line 1:
ORA-00904: "NAMEWRONG": invalid identifier
ORA-06512: at "HR.COLUMN_WRONG_DYNAMIC", line 8
ORA-06512: at line 1

The examples above show that, at compile time, static SQL gives you far stronger guarantee of the correctness of your code than dynamic SQL.

2. Static SQL creates schema object dependencies. Dynamic SQL does not.

On successful compilation of PL/SQL stored procedures with static SQL, Oracle sets up dependencies for objects referenced in the SQL.

With dynamic SQL, no such dependencies are set up.

Lack of dependency information makes it harder to analyze the impact of changes to database objects.

For example, if you need to drop table EMP and, before you do that, want to check how many objects depend on EMP. Oracle is able to point out that the procedure using static SQL depends on it; it does not do the same with dynamic SQL.

SQL> exec dbms_utility.get_dependency('TABLE', 'HR', 'EMP');
-
DEPENDENCIES ON HR.EMP
------------------------------------------------------------
------
*TABLE HR.EMP()
*   PROCEDURE HR.COLUMN_WRONG_STATIC()

PL/SQL procedure successfully completed.

3. Dynamic SQL comes with greater security risks

Beware of SQL injection when dealing with dynamic SQL.

Forget to bind inputs properly and you could find yourself in a mess similar to Little Bobby Table’s school’s:

For more about securing dynamic SQL, read Dynamically Dangerous Code.

4. Static SQL (usually) performs better than Dynamic SQL

All else being equivalent, static SQL has a performance advantage over dynamic SQL since static SQL is parsed and validated only once, and the same can be executed multiple times.

5. Static SQL is easier to read and maintain than Dynamic SQL

This difference sounds trivial when set against weighty matters of security and dependency checking; in reality the unwieldiness of dynamic SQL is often its biggest drawback.

It is hard to read big chunks of dynamic SQL being strung together and understand what the final SQL would look like at runtime. Debugging errors is that much tougher when you cannot readily picture the SQL causing the error.

In large long-running projects, developers come and go — those maintaining the code may not clearly know why something was written a certain way or the full range of scenarios the dynamic SQL works with. In changing complex dynamic SQL, it is all too easy to introduce bugs. And so the overheads of maintenance tend to be a lot higher with dynamic SQL than with static SQL.