Term: EXECUTE IMMEDIATE

Definition:
The Oracle EXECUTE IMMEDIATE statement implements Native Dynamic SQL in Oracle. It provides end-to-end support when executing a dynamic SQL statement or an anonymous PL/SQL block. It substitutes the unknown values, executes the statement, returns the data, and then releases the resources.

EXECUTE IMMEDIATE is useful for creating SQL statements that cannot be symbolized or represented directly in PL/SQL. It can also be used to build statements where you may not (or cannot) know the table names, WHERE clauses, etc ahead of time.

Note that you cannot use EXECUTE IMMEDIATE for multiple-row queries.

Before Oracle 11g, EXECUTE IMMEDIATE and other NDS methods supported SQL string statements up to 32K in length, but Oracle 11g allows the usage of CLOB datatypes as an argument.

Example Syntax:

EXECUTE IMMEDIATE [dynamic SQL string statement without terminator]

 [INTO {define_variable     [, define_variable] ... | record}]

 [USING [IN|OUT|IN OUT] bind_argument [, [IN|OUT|IN OUT] bind_arguments] ];

The dynamic SQL string statement without terminator means just that- the SQL statement cannot have a semicolon (;) at the end of the string. If it does, it will be treated as a standard PL/SQL block.

The SQL string can contain placeholders for bind arguments, but bind values cannot be used to pass in the names of schema objects (table or column names). You may pass in numeric, date, and string expressions, but not a BOOLEAN or NULL literal value

Notes:

The define_variable is a PL/SQL variable or record that captures the result set of the SELECT query, if required. It must be compatible with the data type of the selected columns. BULK_COLLECT must be specified to collect a multi row result set.
Bind arguments are the actual values or local variables which would replace the bind variables in the SQL string statement. The default parameter mode is IN.
The bind argument should be of string, date, or number format, but not of BOOLEAN type.

Example Usage:

BEGIN

EXECUTE IMMEDIATE 'CREATE TABLE NDS_TEST (A NUMBER)';

END;