Dynamic SQL: Building Queries at Runtime

Dynamic SQL is SQL that is constructed at runtime rather than written directly into the code. You build the SQL text as a string — usually inside a PL/SQL procedure — and hand it to the database to parse and execute.

BEGIN
    EXECUTE IMMEDIATE 'SELECT * FROM orders WHERE customer_id = ''C001''';
END;

That statement will run, but it is a bad example — the customer id is hard-coded into the SQL text. Real dynamic SQL uses bind variables:

BEGIN
    EXECUTE IMMEDIATE 'SELECT * FROM orders WHERE customer_id = :cid'
        USING 'C001';
END;

Dynamic SQL is used whenever the shape of the query depends on runtime inputs. A report tool where the user picks the columns. An admin script that operates on a table name passed in from configuration. A DDL statement that PL/SQL otherwise cannot issue. A query whose WHERE clause grows or shrinks based on which filters the user supplied. None of those can be expressed in static SQL.

The trade-off is real. Dynamic SQL is harder to read, harder to debug, and opens the door to injection if you are not careful. Use it when you have to and never when a static query would do the same job.

Flexibility is the main one. Dynamic SQL lets you build queries whose table names, column names, filters, and even join structures depend on values that are only known at runtime.

Four concrete benefits:

• Dynamic object names. A procedure can operate on any table whose name is passed in. Useful for utilities and admin tools.
• Dynamic column lists. A report tool can let the user choose which columns to display.
• Conditional filters. A search screen where the user fills in zero to ten criteria — dynamic SQL builds a query that matches exactly what they asked for.
• DDL from PL/SQL. Statements like CREATE TABLE and ALTER TABLE cannot be written directly inside PL/SQL. Dynamic SQL is the only way to issue them from a procedure.

-- Example: dynamic table creation
BEGIN
    EXECUTE IMMEDIATE 'CREATE TABLE temp_results (
        run_id     NUMBER,
        result_val NUMBER
    )';
END;

That statement creates a table at runtime. Static PL/SQL cannot do it. Every DDL helper tool, every schema migration script, every test setup utility relies on this pattern.

Oracle offers two mechanisms, and the choice between them depends on how complex the dynamic SQL needs to be.

EXECUTE IMMEDIATE is the preferred tool for most cases. It is a single PL/SQL statement that parses and runs one dynamic SQL statement. It handles SELECT, INSERT, UPDATE, DELETE, and DDL, and supports bind variables through the USING clause.

DECLARE
    v_query VARCHAR2(200);
BEGIN
    v_query := 'UPDATE orders SET order_status = :status WHERE order_id = :id';
    EXECUTE IMMEDIATE v_query USING 'Delivered', 1004;
END;

DBMS_SQL is a procedural package for complex scenarios. It gives you a cursor you control step by step — open, parse, bind, execute, fetch, close. The extra control matters when the number of columns or binds is unknown until runtime.

DECLARE
    v_cursor INTEGER;
    v_query  VARCHAR2(200);
BEGIN
    v_query  := 'SELECT COUNT(*) FROM orders WHERE customer_id = :cid';
    v_cursor := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(v_cursor, v_query, DBMS_SQL.NATIVE);
    DBMS_SQL.BIND_VARIABLE(v_cursor, ':cid', 'C001');
    -- fetch, define, execute, close ...
END;

The rule: use EXECUTE IMMEDIATE whenever it can do the job. Reach for DBMS_SQL only when the statement's shape is truly unknown at compile time.

Bind variables. Every value that comes from outside the program goes through a bind, never through string concatenation. That is the rule and it has no exceptions.

-- VULNERABLE: user input becomes part of the SQL text
v_sql := 'SELECT * FROM customers WHERE customer_name = ''' || p_name || '''';

-- SAFE: user input is passed as a value, not as SQL
v_sql := 'SELECT * FROM customers WHERE customer_name = :name';
EXECUTE IMMEDIATE v_sql USING p_name;

The difference is subtle but total. In the first version, if the user supplies Alice Corp' OR '1'='1, the SQL text becomes WHERE customer_name = 'Alice Corp' OR '1'='1' — which is always true and returns every row. In the second version, the same input is treated as a literal string. The database looks for a customer whose name is literally Alice Corp' OR '1'='1, which does not exist, so no rows come back.

Bind variables also help performance. The SQL text stays the same across executions, so the database caches and reuses the execution plan instead of parsing fresh every time.

The one thing bind variables cannot handle is object names — table names and column names. Those have to be concatenated into the SQL text. For identifiers, validate against a whitelist before using them.

Five limitations come up regularly. None of them are deal-breakers, but all of them shape when dynamic SQL is the right tool.

Harder to read and debug. Static SQL is visible in the source code. Dynamic SQL only exists as text at runtime, so you have to print it or log it to see what was actually executed.

No compile-time syntax checking. The compiler does not parse the SQL text because it does not exist yet. Syntax errors, missing tables, wrong column names — all surface as runtime errors.

Performance cost without binds. Concatenating values into the SQL text produces a different SQL statement every time, so the database has to parse and plan it fresh. Bind variables fix this, but only for values.

Explicit permission handling. The database checks permissions at runtime, not at compile time. If the executing user lacks SELECT on a table the dynamic SQL references, the statement fails only when it runs.

-- This fails at runtime, not at compile time
v_sql := 'SELECT * FROM table_the_user_cannot_read';   -- compiles fine
EXECUTE IMMEDIATE v_sql;                                -- fails

SQL injection risk. Any time user input flows into the SQL text, there is a risk. Bind variables mitigate this, but they do not cover identifiers.

EXECUTE IMMEDIATE is a single statement. DBMS_SQL is a package of procedures. The first runs one dynamic SQL statement end to end. The second gives you a cursor you control step by step.

Aspect	EXECUTE IMMEDIATE	DBMS_SQL
Complexity	Simple	Verbose
Best for	Single statements with known shape	Dynamic cursors, unknown shapes
Returns a result	Via INTO	Via FETCH into a define buffer
Number of columns	Must be known at compile time	Can be discovered at runtime
Bulk operations	Limited	Full BIND_ARRAY support
Plan caching	Yes, with binds	Yes, with binds

Use EXECUTE IMMEDIATE whenever it can express the dynamic SQL. That covers the vast majority of cases — DDL, DML with a known set of columns, queries with a fixed result shape.

Reach for DBMS_SQL only when the statement's shape is not known until runtime. A query whose column list comes from the database metadata. A bind list that varies by call. Bulk operations with dynamically-sized arrays.

Oracle PL/SQL does not let you write DDL directly inside a procedure. Statements like CREATE TABLE, ALTER TABLE, and DROP TABLE fail to compile because PL/SQL is designed to run against a fixed schema.

The workaround is to pass the DDL text to EXECUTE IMMEDIATE:

BEGIN
    EXECUTE IMMEDIATE 'ALTER TABLE customers ADD (contact_email VARCHAR2(100))';
END;

The database parses and runs the DDL at runtime. The procedure compiles normally because the compiler never sees the DDL inside its body.

One important caveat — DDL auto-commits. On Oracle, running any DDL statement commits the current transaction as a side effect, and the DDL itself cannot be rolled back. If your procedure has pending DML that you were planning to roll back, the DDL will commit it anyway.

BEGIN
    UPDATE customers SET credit_limit = 0 WHERE customer_id = 'C001';
    -- The next statement commits the update above, even before it runs
    EXECUTE IMMEDIATE 'CREATE TABLE temp_table (id NUMBER)';
    ROLLBACK;   -- Does not undo the CREATE TABLE or the earlier UPDATE
END;

That is one reason DDL-heavy procedures are usually structured to run DDL first, then DML, or to run the whole thing in a context where a partial failure is not catastrophic.

Build the WHERE clause incrementally, adding each condition only when the corresponding parameter is supplied. A common trick is to start with WHERE 1=1 so every subsequent fragment can start with AND without worrying whether it is the first condition.

DECLARE
    v_query      VARCHAR2(400);
    v_dept_id    NUMBER;
    v_min_salary NUMBER;
BEGIN
    v_query := 'SELECT customer_name FROM customers WHERE 1=1';

    IF v_region IS NOT NULL THEN
        v_query := v_query || ' AND region = :region';
    END IF;

    IF v_min_credit IS NOT NULL THEN
        v_query := v_query || ' AND credit_limit >= :min_credit';
    END IF;

    EXECUTE IMMEDIATE v_query USING v_region, v_min_credit;
END;

Two things are worth noting. First, every value goes through a bind variable — nothing is concatenated directly. Second, the USING clause has to list bind values in the order the markers appear in the text. If a marker is added conditionally, the corresponding value has to be added in the same order.

The pattern scales to any number of optional conditions. Search screens with ten possible filters all build the same way.

The single most useful technique is to print the SQL text before you run it. Because the SQL is built at runtime, the source code does not show what was actually executed. Printing it does.

DECLARE
    v_query VARCHAR2(400);
BEGIN
    v_query := 'SELECT * FROM customers WHERE region = :region';
    -- Print before executing
    DBMS_OUTPUT.PUT_LINE('Generated SQL: ' || v_query);
    EXECUTE IMMEDIATE v_query USING 'North';
END;

Enable DBMS_OUTPUT in your client first. In SQL*Plus:

SET SERVEROUTPUT ON SIZE UNLIMITED

In SQL Developer, open the DBMS Output panel and connect it to the session.

For jobs that run unattended — nightly batches, scheduled procedures — printing to the console is useless because nobody is watching. Log the SQL text to a table instead:

INSERT INTO dyn_sql_log (sql_text, logged_at, session_user_id)
VALUES (v_query, SYSDATE, USER);
COMMIT;

Then query the log the next morning when something went wrong. That is often the only way to trace what a runtime-generated statement was actually doing.

The most common bugs you will find this way: missing spaces between concatenated fragments, values that were not properly quoted, bind markers that do not match the USING clause, and reserved words used as column names.

Dynamic SQL shows up in a handful of recurring scenarios. Recognising them makes it easier to decide when to reach for it.

Dynamic report generation. A reporting tool that lets the user choose columns, filters, groupings, and sort orders. Static SQL cannot express an unknown column list.

v_sql := 'SELECT ' || p_columns || ' FROM orders WHERE ' || p_filter;

Administrative utilities. A script that operates on any table whose name comes from configuration. Table statistics collection, index rebuilds, data exports — all of them need the target table name at runtime.

Schema management. Procedures that create, alter, or drop tables as part of a migration. Anything that issues DDL has to go through dynamic SQL because PL/SQL does not support DDL directly.

Dynamic column selection. A query that picks which columns to return based on a runtime variable:

DECLARE
    v_column VARCHAR2(50) := 'salary';
    v_sql    VARCHAR2(200);
BEGIN
    v_sql := 'SELECT ' || v_column || ' FROM employees';
    EXECUTE IMMEDIATE v_sql;
END;

Data movement between tables. ETL jobs where the source and destination tables are chosen from a config table. Copy the data, apply the transformation, insert into the target — all driven by runtime names.

In every one of those cases, the same discipline applies: values go through bind variables, identifiers go through a whitelist, and the SQL text is logged or printed for debugging.

No. Bind variables are for values, not identifiers. The database has to know the table name at parse time so it can build a plan. A bind variable's value is not known until after parsing, so it cannot be used where an identifier is expected.

-- This is a syntax error
v_sql := 'SELECT COUNT(*) FROM :table_name';
EXECUTE IMMEDIATE v_sql USING 'customers';

-- Identifiers have to be concatenated
v_sql := 'SELECT COUNT(*) FROM ' || v_table_name;

That leaves dynamic SQL with a problem: the identifier comes from outside the program (usually from a user or a config file), and it goes into the SQL text without protection. This is where SQL injection lives when values are properly bound.

The solution is validation against a whitelist. Build a list of valid table names and check the input against it:

IF p_table_name IN ('CUSTOMERS', 'ORDERS', 'ACCOUNTS') THEN
    v_table_name := p_table_name;
ELSE
    RAISE_APPLICATION_ERROR(-20001, 'Invalid table name: ' || p_table_name);
END IF;

If the input matches, it can be safely concatenated. If it does not, the procedure stops with an error before any SQL runs.

Oracle also provides DBMS_ASSERT.SIMPLE_SQL_NAME as an additional check. It throws an exception if the string contains anything that is not a valid SQL identifier. Combined with a whitelist, that is a strong defence.

Native dynamic SQL is the PL/SQL feature that lets you build and run SQL statements at runtime without going through the DBMS_SQL package. The syntax is direct:

BEGIN
    EXECUTE IMMEDIATE 'SELECT ...';
END;

It was introduced in Oracle 8i to replace the more verbose DBMS_SQL approach. The "native" part means it is built into the PL/SQL language itself, not a package you have to call.

Native dynamic SQL covers three main forms:

• EXECUTE IMMEDIATE — the general-purpose statement for any dynamic SQL, with INTO, USING, and RETURNING INTO clauses.
• OPEN FOR — opens a dynamic cursor you can fetch from one row at a time.
• Dynamic bulk operations — EXECUTE IMMEDIATE with BULK COLLECT INTO for fetching many rows at once.

-- OPEN FOR pattern: dynamic cursor
DECLARE
    v_cursor  SYS_REFCURSOR;
    v_name    VARCHAR2(100);
BEGIN
    OPEN v_cursor FOR 'SELECT customer_name FROM customers WHERE region = :r' USING 'North';
    LOOP
        FETCH v_cursor INTO v_name;
        EXIT WHEN v_cursor%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_name);
    END LOOP;
    CLOSE v_cursor;
END;

That pattern handles dynamic queries that return multiple rows with an unknown number of results. It is a middle ground between EXECUTE IMMEDIATE (which needs a single row into a fixed INTO list) and DBMS_SQL (which gives you the most control but the most code).

For 99% of dynamic SQL tasks, native dynamic SQL is enough. The remaining 1% — truly unknown column counts, dynamic bind arrays — needs DBMS_SQL.

If user input flows into the SQL text without a bind variable, you have a SQL injection vulnerability. The user can supply text that changes the meaning of the query.

-- Vulnerable
v_sql := 'SELECT * FROM customers WHERE customer_name = ''' || p_name || '''';

Attackers exploit this in three common ways.

Bypassing filters. Supply Alice Corp' OR '1'='1 and the query returns every row in the table. Useful for reading data the attacker should not be able to access.

Union-based extraction. Supply text that closes the current query and unions in another one. Read data from tables the application never intended to expose.

Stacked statements. Some databases allow multiple statements in one string. The attacker appends ; DROP TABLE customers; -- and the database runs both.

v_sql := 'SELECT * FROM customers WHERE customer_name = '''
         || p_name
         || '''';

-- With p_name = "x'; DROP TABLE orders; --"
-- The SQL becomes:
-- SELECT * FROM customers WHERE customer_name = 'x';
-- DROP TABLE orders; --'

Whether the attack is possible depends on the database's configuration, but the defence is the same everywhere — never concatenate values. Use bind variables.

There is one scenario where concatenation cannot be avoided: identifiers. Table names and column names have to be part of the SQL text. For those, whitelist validation is the only safe approach.

The bind variable IN list is a classic problem in dynamic SQL. You cannot bind a variable number of values in a single bind variable:

-- This does NOT work
WHERE customer_id IN (:ids);   -- :ids would be treated as a single value

Three approaches handle it. The choice depends on the number of values and how much you care about plan reuse.

Approach 1 — concatenate the values (safe if they are not user-supplied).

v_sql := 'SELECT * FROM customers WHERE customer_id IN (';
v_sql := v_sql || '''' || 'C001' || ''', ''' || 'C002' || '''';
v_sql := v_sql || ')';

Fast to write, but only safe when the values come from a trusted source — a hard-coded list, a previous query result. Never from user input.

Approach 2 — build one bind marker per value.

v_sql := 'SELECT * FROM customers WHERE customer_id IN (';
FOR i IN 1 .. v_ids.COUNT LOOP
    v_sql := v_sql || ':id' || i;
    IF i < v_ids.COUNT THEN
        v_sql := v_sql || ', ';
    END IF;
END LOOP;
v_sql := v_sql || ')';

EXECUTE IMMEDIATE v_sql USING v_ids(1), v_ids(2), v_ids(3);

Safe and reusable. The downside is that the number of binds changes the SQL text, so a list of three values and a list of four are different statements with different plans.

Approach 3 — use a collection with a table function.

CREATE TYPE id_list_t AS TABLE OF VARCHAR2(10);

SELECT * FROM customers
WHERE customer_id IN (SELECT COLUMN_VALUE FROM TABLE(id_list_t('C001', 'C002', 'C003')));

Single SQL text for any number of values. Plan reuse is best. The setup is more involved — you have to define the collection type and construct it in the calling code — but for hot queries it is worth it.

Dynamic SQL runs inside the same transaction as the calling code. If you are in a transaction and issue an EXECUTE IMMEDIATE that performs an INSERT, that insert is part of the transaction. A subsequent ROLLBACK undoes it, just like a static insert.

BEGIN
    EXECUTE IMMEDIATE 'INSERT INTO orders (order_id, customer_id) VALUES (1008, ''C001'')';
    -- Still inside the transaction
    ROLLBACK;   -- The insert is undone
END;

The exception is DDL. On Oracle, DDL statements auto-commit the transaction before they run and again after they finish. If you have pending DML and then run DDL through dynamic SQL, the pending DML commits first.

BEGIN
    INSERT INTO customers (customer_id, customer_name) VALUES ('C008', 'Henry Co');
    EXECUTE IMMEDIATE 'CREATE TABLE temp_table (id NUMBER)';
    -- The INSERT above is now committed, even though we have not issued COMMIT
    ROLLBACK;   -- Does not undo the INSERT or the CREATE TABLE
END;

That behaviour is specific to DDL. DML through dynamic SQL is transactionally identical to static DML.

There is also an autocommit option in some client tools (SQL*Plus with SET AUTOCOMMIT ON) that commits after every statement. Do not confuse that with DDL behaviour — it is a client setting, not a database rule.

Every distinct SQL text that the database parses gets an entry in the shared pool — Oracle's cache of parsed statements and plans. Dynamic SQL can churn that cache if you are not careful, because a slightly different SQL text creates a different entry.

-- Each of these produces a different entry
'SELECT * FROM orders WHERE customer_id = ''C001'''
'SELECT * FROM orders WHERE customer_id = ''C002'''
'SELECT * FROM orders WHERE customer_id = ''C003'''
-- ...thousands more, one per distinct customer id

On a busy system, that churn evicts useful plans from the shared pool and forces other queries to re-parse. It is called "shared pool fragmentation" and it is a real performance problem.

Bind variables fix it. The SQL text stays the same, so it is one shared pool entry reused for every customer id:

'SELECT * FROM orders WHERE customer_id = :cid'

The remaining source of churn is identifiers. Table names and column names cannot be bound, so a utility that operates on twenty tables produces twenty different SQL texts. That is usually acceptable because the utility is not called a million times per second — but it is worth knowing about.

If you see "hard parse" counts climbing and the shared pool filling up, dynamic SQL without binds is a common cause. Switching to binds is often a one-line fix that resolves the problem.

Permissions on dynamic SQL are checked at runtime, not at compile time. Static SQL fails to compile if the executing user lacks SELECT on a referenced table. Dynamic SQL compiles fine and fails only when the statement runs.

-- Compiles fine even if the user cannot read this table
v_sql := 'SELECT * FROM sensitive_data';
EXECUTE IMMEDIATE v_sql;   -- Fails at runtime with ORA-00942

The exceptions you can see:

• ORA-00942 — table or view does not exist (or the user cannot see it).
• ORA-01031 — insufficient privileges.
• PLS-00905 — object is invalid or the user does not have execute permission on a referenced PL/SQL unit.

Two ways to grant permissions for dynamic SQL.

Direct grant. Grant the executing user the privileges they need on the underlying objects. Simple, but every user of the procedure needs the same grants.

Definer's rights. Declare the procedure with AUTHID DEFINER — the default — so that the procedure runs with the privileges of its owner, not the caller. Then the caller does not need direct access to the tables. This is the standard approach for controlled access.

CREATE OR REPLACE PROCEDURE get_customer_count
AUTHID DEFINER
AS
    v_count NUMBER;
BEGIN
    EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM customers' INTO v_count;
    DBMS_OUTPUT.PUT_LINE(v_count);
END;

The alternative is AUTHID CURRENT_USER, where the procedure runs with the caller's privileges. Choose based on whether you want to control access by granting it to the procedure owner or to each caller.

There is a right shape and a wrong shape. The right one uses bind variables for every value, validates identifiers against a whitelist, and logs the SQL text before running it.

CREATE OR REPLACE PROCEDURE customer_count_by_region (
    p_region IN VARCHAR2,
    p_count  OUT NUMBER
) AS
    v_sql VARCHAR2(400);
BEGIN
    -- Use bind variables for all values
    v_sql := 'SELECT COUNT(*) '
          || 'FROM customers '
          || 'WHERE region = :region';

    -- Log for debugging
    DBMS_OUTPUT.PUT_LINE('Executing: ' || v_sql);

    -- Execute with the bind
    EXECUTE IMMEDIATE v_sql
        INTO p_count
        USING p_region;

EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
        RAISE;
END;
/

The pieces of that template:

• Build the SQL text in a variable, not directly in EXECUTE IMMEDIATE — makes it easier to log.
• Use bind markers (:region) for every value, not string concatenation.
• Log the SQL before running it, at least during development.
• Handle exceptions so the procedure does not crash without a trace.

When identifiers are involved, add a whitelist check before the SQL text is built:

IF p_region NOT IN ('North', 'South', 'East', 'West') THEN
    RAISE_APPLICATION_ERROR(-20001, 'Invalid region: ' || p_region);
END IF;

That combination — bind values, whitelist identifiers, log the SQL, handle errors — is the standard shape for safe dynamic SQL. Everything else is a variation on the same template.