EXECUTE IMMEDIATE Statement

The EXECUTE IMMEDIATE statement builds and runs a dynamic SQL statement in a single operation. The EXECUTE IMMEDIATE statement is the means by which native dynamic SQL processes most dynamic SQL statements.

Topics:

• Syntax

• Semantics

• Examples

• Related Topics

Syntax

execute_immediate_statement ::=

See:

• bulk_collect_into_clause ::=

• dynamic_returning_clause ::=

• into_clause ::=

using_clause ::=

Semantics

execute_immediate_statement

dynamic_sql_stmt

A string literal, string variable, or string expression that represents a SQL statement. Its type must be either CHAR, VARCHAR2, or CLOB.

into_clause

Specifies the variables or record in which to store the column values that the statement returns. For more information about this clause, see "RETURNING INTO Clause".

Restriction on into_clause Use if and only if dynamic_sql_stmt returns a single row.

bulk_collect_into_clause

Specifies one or more collections in which to store the rows that the statement returns. For more information about this clause, see "RETURNING INTO Clause".

Restriction on bulk_collect_into_clause Use if and only if dynamic_sql_stmt can return multiple rows.

using_clause

Specifies bind arguments.

Restrictions on using_clause 

• Use if and only if dynamic_sql_stmt includes placeholders for bind arguments.

• If dynamic_sql_stmt has a RETURNING INTO clause, using_clause can contain only IN bind arguments. The bind arguments in the RETURNING INTO clause are OUT bind arguments by definition.

dynamic_returning_clause

Returns the column values of the rows affected by the dynamic SQL statement, in either individual variables or records. For more information about this clause, see "RETURNING INTO Clause".

Restriction on dynamic_returning_clause Use if and only if dynamic_sql_stmt has a RETURNING INTO clause.

using_clause

IN, OUT, IN OUT

Parameter modes of bind arguments. An IN bind argument passes its value to dynamic_sql_stmt. An OUT bind argument stores a value that dynamic_sql_stmt returns. An IN OUT bind argument passes its initial value to dynamic_sql_stmt and stores a value that dynamic_sql_stmt returns. The default parameter mode for bind_argument is IN.

bind_argument

An expression whose value replaces its corresponding placeholder in dynamic_sql_stmt at run time.

Every placeholder in dynamic_sql_stmt must be associated with a bind_argument in the USING clause or RETURNING INTO clause (or both) or with a define variable in the INTO clause.

You can run dynamic_sql_stmt repeatedly using different values for the bind arguments. You incur some overhead, because EXECUTE IMMEDIATE prepares the dynamic string before every execution.

Restriction on bind_argument The value of bind_argument cannot be TRUE, FALSE, or NULL. To pass the value NULL to the dynamic SQL statement, use an uninitialized variable where you want to use NULL, as in "Uninitialized Variable for NULL in USING Clause".

Examples

• Example 7-1, "Invoking a Subprogram from a Dynamic PL/SQL Block"

• Example 7-2, "Unsupported Data Type in Native Dynamic SQL"

• Example 7-3, "Uninitialized Variable for NULL in USING Clause"

• Example 7-5, "Repeated Placeholder Names in Dynamic PL/SQL Block"

Related Topics

In this chapter:

• "RETURNING INTO Clause"

In other chapters:

• "EXECUTE IMMEDIATE Statement"

• "DBMS_SQL Package"