SQL IMMEDIATE

What does SQL EXECUTE IMMEDIATE do?

Executes a dynamically constructed SQL statement immediately without prior preparation.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL IMMEDIATE: Oracle (PL/SQL), PostgreSQL (PL/pgSQL), MySQL 8.0+, MariaDB 10.2+, IBM Db2, SAP HANA. Not supported natively in SQL Server or SQLite (use EXEC / sp_executesql or PRAGMA).

SQL IMMEDIATE Full Explanation

IMMEDIATE is used in the EXECUTE IMMEDIATE statement to run dynamic SQL. Instead of compiling a statement ahead of time with PREPARE, the database parses, optimizes, and executes the supplied character string on the spot. This allows you to build SQL at runtime, substitute bind variables, and run DDL, DML, or PL/SQL blocks that cannot be parameterized through ordinary prepared statements. While powerful, EXECUTE IMMEDIATE bypasses some compile-time checks, so errors surface only when the string is executed. It should therefore be combined with bind variables and rigorous validation to limit SQL injection risks. In transactional contexts the executed statement behaves like any other SQL command: it can be committed, rolled back, and it respects the current isolation level.

SQL IMMEDIATE Syntax

EXECUTE IMMEDIATE <string_expression>
[ INTO <variable_list> ]
[ USING <bind_arg> [, ...] ];

SQL IMMEDIATE Parameters

string_expression (STRING) - The SQL text to execute at runtime.
variable_list (Variable(s)) - Optional target variables for SELECT results (Oracle).
bind_arg (ANY) - Values substituted into positional or named placeholders inside the string.

Example Queries Using SQL IMMEDIATE

-- Oracle: create a table whose name is supplied at runtime
DECLARE
  v_sql VARCHAR2(200);
BEGIN
  v_sql := 'CREATE TABLE temp_' || TO_CHAR(SYSDATE,'YYYYMMDD') || ' (id NUMBER)';
  EXECUTE IMMEDIATE v_sql;
END;
/

-- PostgreSQL PL/pgSQL: run a dynamic UPDATE
EXECUTE IMMEDIATE 'UPDATE ' || quote_ident(tablename) ||
                  ' SET processed = TRUE WHERE id = $1' USING rec_id;

-- MySQL stored procedure: drop a table passed as a parameter
SET @sql = CONCAT('DROP TABLE IF EXISTS ', QUOTE_IDENTIFIER(p_table));
EXECUTE IMMEDIATE @sql;

Expected Output Using SQL IMMEDIATE

The database parses the constructed string, executes it, and returns normal success or error messages
DDL takes effect immediately; DML affects rows and can be committed or rolled back

Use Cases with SQL IMMEDIATE

Building table or column names dynamically
Executing DDL inside stored procedures
Running flexible search filters whose columns are not known until runtime
Migrating data when object names vary by tenant or date
Bulk maintenance scripts that loop through metadata tables

Common Mistakes with SQL IMMEDIATE

Concatenating user input directly into the string, leading to SQL injection
Forgetting to use bind variables, which degrades performance and security
Assuming compile-time checks; syntax errors appear only at execution
Omitting required privileges for the dynamic statement
Overusing dynamic SQL when static SQL would suffice

First Introduced In

SQL/92 dynamic SQL feature; popularised in Oracle 8i

Frequently Asked Questions

What is the difference between PREPARE...EXECUTE and EXECUTE IMMEDIATE?

PREPARE creates a reusable execution plan that can be run many times, while EXECUTE IMMEDIATE parses and runs the string once in a single step.

Can I use bind variables with EXECUTE IMMEDIATE?

Yes. Most dialects let you specify USING clauses so values are bound safely and efficiently.

Why does my EXECUTE IMMEDIATE fail with ORA-00900: invalid SQL statement?

The dynamic string probably has a syntax error or is missing required privileges. Print the string, run it manually, and verify syntax.

Does EXECUTE IMMEDIATE commit automatically?

No. It follows the surrounding transaction rules. You control commits and rollbacks explicitly.