What is the EXECUTE IMMEDIATE statement?

Are you preparing for IT certification? With practice questions, study notes, interactive quizzes, tips and technical articles, uCertify PrepKits ensure that you get a solid grasp of core technical concepts to ace your certification exam in first attempt.

What is the EXECUTE IMMEDIATE statement?

Rating:

The EXECUTE IMMEDIATE statement prepares (parses) and immediately executes a dynamic SQL statement or an anonymous PL/SQL block.

Syntax:

EXECUTE IMMEDIATE dynamic_string
[INTO {define_variable[, define_variable]... | record}]
[USING [IN | OUT | IN OUT] bind_argument[, [IN | OUT | IN OUT] bind_argument]...]
[{RETURNING | RETURN} INTO bind_argument[, bind_argument]...];

where,

dynamic_string is a string expression that represents a SQL statement (without terminator (;)) or PL/SQL block (with terminator (;)).

The INTO clause specifies the variables or records into which column values are retrieved. It is used only for single-row queries. For each value retrieved by the query, there must be a corresponding, type-compatible variable or field in the INTO clause.

define_variable is a variable that stores a selected column value.

record is a user-defined or %ROWTYPE record that stores a selected row.

The USING clause is used to hold all bind arguments. The default parameter mode is IN.

The RETURNING INTO clause specifies the variable into which column values are returned. It is used only for DML statements that have a RETURNING clause (without a BULK COLLECT clause). For each value returned by a DML statement, there must be a corresponding, type-compatible variable in the RETURNING INTO clause.

bind_argument is an expression whose value is passed to the dynamic SQL statement or PL/SQL block.

Rating: