Dynamic SQL in PL/SQL – Complete Guide with Examples

Oracle PL/SQL supports Dynamic SQL for executing SQL statements that are not known until runtime. Unlike static SQL that is precompiled, Dynamic SQL in PL/SQL offers flexibility to build and run SQL statements dynamically using either the DBMS_SQL package or Native Dynamic SQL (NDS).

This guide explains both approaches with syntax and real-world examples.

Introduction to Dynamic SQL in PL/SQL

Dynamic SQL refers to SQL statements that are constructed and executed during runtime, rather than being embedded statically in the code. This makes it powerful for cases where the structure of SQL is not known until execution time—like dynamic DDL operations or flexible WHERE clauses.

Oracle offers two primary methods for using Dynamic SQL in PL/SQL:

DBMS_SQL package (older but powerful)
EXECUTE IMMEDIATE (NDS – Native Dynamic SQL, simpler and faster)

Native Dynamic SQL in PL/SQL using EXECUTE IMMEDIATE

Native Dynamic SQL (NDS) was introduced in Oracle 8i and revolutionized the way developers write dynamic code.

It uses a single, elegant syntax:

EXECUTE IMMEDIATE dynamic_sql_string;

Example – Disable All Triggers in Current Schema

SET SERVEROUTPUT ON
DECLARE
  v_statement VARCHAR2(500);
  CURSOR trigger_cur IS
    SELECT trigger_name FROM user_triggers;
BEGIN
  FOR y IN trigger_cur LOOP
    v_statement := 'ALTER TRIGGER ' || y.trigger_name || ' DISABLE';
    EXECUTE IMMEDIATE v_statement;
  END LOOP;
END;
/

Explanation:

The cursor retrieves all trigger names.
A dynamic ALTER TRIGGER statement is built inside the loop.
EXECUTE IMMEDIATE executes it dynamically.

Using DBMS_SQL for Dynamic SQL in PL/SQL

Before EXECUTE IMMEDIATE existed, the DBMS_SQL package was the only way to run dynamic SQL in PL/SQL. It is still useful for:

Dynamic queries with unknown number/type of columns
When binding variables dynamically

Although it involves more steps, it provides flexibility.

Basic Steps for DBMS_SQL:

Open a cursor
Parse the SQL
Bind variables (if any)
Execute
Fetch results

You can find a working example in Oracle’s official documentation:
🔗 Oracle DBMS_SQL Documentation

DBMS_SQL vs EXECUTE IMMEDIATE – Comparison

Feature	EXECUTE IMMEDIATE (NDS)	DBMS_SQL
Ease of Use	✅ Simple	❌ Verbose
Performance	✅ Fast (compiled)	⚠️ Slower
Variable Bind Support	✅ Yes	✅ Yes
Returning Result Sets	❌ Limited	✅ Supported
Suitable for Dynamic DDL	✅ Yes	✅ Yes

Why Use Dynamic SQL in PL/SQL?

Use Dynamic SQL in PL/SQL when:

You need to run DDL statements (e.g., CREATE TABLE, DROP USER).
You want to loop over object names (e.g., disabling multiple triggers).
You need flexibility in WHERE clauses or table names at runtime.

Best Practices for Dynamic SQL in PL/SQL

Use EXECUTE IMMEDIATE for simple dynamic SQL and DDL.
Use DBMS_SQL when:
- You need to return result sets with unknown structure.
- You’re working with SQL statements with varying number of bind variables.
Always validate dynamic input to avoid SQL injection.

AmantPoint Exclusive Learning Series
© 2025 AmantPoint. All rights reserved. Educational content for developers and learners.