Skip Headers
Oracle® Database PL/SQL Language Reference
11g Release 1 (11.1)

B28370-05
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

EXECUTE IMMEDIATE Statement

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

Syntax

execute_immediate_statement ::=

execute_immediate_statement
Description of the illustration execute_immediate_statement.gif

into_clause ::=

into_clause
Description of the illustration into_clause.gif

bulk_collect_into_clause ::=

bulk_collect_into_clause
Description of the illustration bulk_collect_into_clause.gif

using_clause ::=

using_clause
Description of the illustration using_clause.gif

Keyword and Parameter Descriptions

bind_argument

Either an expression whose value is passed to the dynamic SQL statement (an in bind), or a variable in which a value returned by the dynamic SQL statement is stored (an out bind).

BULK COLLECT INTO

Used if and only if dynamic_sql_stmt can return multiple rows, this clause specifies one or more collections in which to store the returned rows. This clause must have a corresponding, type-compatible collection_item or :host_array_name for each select_item in dynamic_sql_stmt.

collection_name

The name of a declared collection, in which to store rows returned by dynamic_sql_stmt.

dynamic_returning_clause

Used if and only if dynamic_sql_stmt has a RETURNING INTO clause, this clause returns the column values of the rows affected by dynamic_sql_stmt, in either individual variables or records (eliminating the need to select the rows first). This clause can include OUT bind arguments. For details, see RETURNING INTO Clause.

dynamic_sql_stmt

A string literal, string variable, or string expression that represents any SQL statement. It must be of type CHAR, VARCHAR2, or CLOB.

host_array_name

An array into which returned rows are stored. The array must be declared in a PL/SQL host environment and passed to PL/SQL as a bind argument (hence the colon (:) prefix).

IN, OUT, IN OUT

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

INTO

Used if and only if dynamic_sql_stmt is a SELECT statement that can return at most one row, this clause specifies the variables or record into which the column values of the returned row are stored. For each select_item in dynamic_sql_stmt, this clause must have either a corresponding, type-compatible define_variable or a type-compatible record.

record_name

A user-defined or %ROWTYPE record into which a returned row is stored.

USING

Used only if dynamic_sql_stmt includes placeholders, this clause specifies a list of bind arguments.

variable_name

The name of a define variable in which to store a column value of the row returned by dynamic_sql_stmt.

Usage Notes

For DML statements that have a RETURNING clause, you can place OUT bind arguments in the RETURNING INTO clause without specifying the parameter mode, which, by definition, is OUT. If you use both the USING clause and the RETURNING INTO clause, the USING clause can contain only IN arguments.

At run time, bind arguments or define variables replace corresponding placeholders in the dynamic SQL statement. Every placeholder 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.

The value a of bind argument cannot be a Boolean literal (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.

You can execute a dynamic SQL statement repeatedly using new values for the bind arguments. You incur some overhead, because EXECUTE IMMEDIATE prepares the dynamic string before every execution.

Note:

When using dynamic SQL, be aware of SQL injection, a security risk. For more information about SQL injection, see Avoiding SQL Injection in PL/SQL.

Examples

Related Topics