Available in versions: Dev (3.21) | Latest (3.20) | 3.19 | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12
EXECUTE statement
Applies to ❌ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
Many dialects support some way of running dynamic SQL from procedural code. For this, the EXECUTE or EXECUTE IMMEDIATE statements can be used.
In some dialects (e.g. Oracle PL/SQL), using EXECUTE is the only way to run DDL from procedural code.
For example:
-- PL/SQL BEGIN EXECUTE IMMEDIATE 'CREATE TABLE t (col int)'; END;
// All dialects
create.begin(
execute(createTable("t").column("col", INTEGER).getSQL())
).excute();
You could obviously just pass an arbitrary string to the EXECUTE statement, as in PL/SQL, but the above example shows how to use this approach also with dynamically created jOOQ statements, by calling Query.getSQL().
Dialect support
This example using jOOQ:
execute("create table t (i int)")
Translates to the following dialect specific expressions:
Aurora Postgres, Postgres, YugabyteDB
EXECUTE 'create table t (i int)'
BigQuery, DB2, Hana, MariaDB, Oracle, Snowflake
EXECUTE IMMEDIATE 'create table t (i int)'
Firebird
EXECUTE STATEMENT 'create table t (i int)'
MySQL
CREATE PROCEDURE block_1744736953753_3402918() MODIFIES SQL DATA BEGIN PREPARE s FROM 'create table t (i int)'; EXECUTE s; DEALLOCATE PREPARE s; END; CALL block_1744736953753_3402918(); DROP PROCEDURE block_1744736953753_3402918;
SQLServer
EXECUTE ('create table t (i int)')
ASE, Access, Aurora MySQL, ClickHouse, CockroachDB, Databricks, Derby, DuckDB, Exasol, H2, HSQLDB, Informix, MemSQL, Redshift, SQLDataWarehouse, SQLite, Sybase, Teradata, Trino, Vertica
/* UNSUPPORTED */
Generated with jOOQ 3.21. Support in older jOOQ versions may differ. Translate your own SQL on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!