|Oracle® Database PL/SQL User's Guide and Reference
10g Release 2 (10.2)
|PDF · Mobi · ePub|
IMMEDIATE statement executes a dynamic SQL statement or anonymous PL/SQL block. You can use it to issue SQL statements that cannot be represented directly in PL/SQL, or to build up statements where you do not know all the table names, WHERE clauses, and so on in advance. For more information, see "Using the EXECUTE IMMEDIATE Statement in PL/SQL".
An expression whose value is passed to the dynamic SQL statement, or a variable that stores a value returned by the dynamic SQL statement.
Stores result values in one or more collections, for faster queries than loops with
FETCH statements. For more information, see "Reducing Loop Overhead for DML Statements and Queries with Bulk SQL".
A declared collection into which
select_item values are fetched. For each
select_item, there must be a corresponding, type-compatible collection in the list.
An array (declared in a PL/SQL host environment and passed to PL/SQL as a bind variable) into which
select_item values are fetched. For each
select_item, there must be a corresponding, type-compatible array in the list. Host arrays must be prefixed with a colon.
A variable that stores a selected column value.
A string literal, variable, or expression that represents a single SQL statement or a PL/SQL block. It must be of type
Used only for single-row queries, this clause specifies the variables or record into which column values are retrieved. For each value retrieved by the query, there must be a corresponding, type-compatible variable or field in the
A user-defined or
%ROWTYPE record that stores a selected row.
Returns values from inserted rows, eliminating the need to
SELECT the rows afterward. You can retrieve the column values into variables or into collections. You cannot use the
RETURNING clause for remote or parallel inserts. If the statement does not affect any rows, the values of the variables specified in the
RETURNING clause are undefined. For the syntax of
returning_clause, see the "RETURNING INTO Clause".
Specifies a list of input and/or output bind arguments. The parameter mode defaults to
Except for multi-row queries, the dynamic string can contain any SQL statement (without the final semicolon) or any PL/SQL block (with the final semicolon). The string can also contain placeholders for bind arguments. You cannot use bind arguments to pass the names of schema objects to a dynamic SQL statement.
You can place all bind arguments in the
USING clause. The default parameter mode is
IN. For DML statements that have a
RETURNING clause, you can place
OUT arguments in the
INTO clause without specifying the parameter mode, which, by definition, is
OUT. If you use both the
USING clause and the
INTO clause, the
USING clause can contain only
At run time, bind arguments replace corresponding placeholders in the dynamic string. Every placeholder must be associated with a bind argument in the
USING clause and/or
INTO clause. You can use numeric, character, and string literals as bind arguments, but you cannot use Boolean literals (
NULL). To pass nulls to the dynamic string, you must use a workaround. See "Passing Nulls to Dynamic SQL".
Dynamic SQL supports all the SQL datatypes. For example, define variables and bind arguments can be collections,
LOBs, instances of an object type, and refs. Dynamic SQL does not support PL/SQL-specific types. For example, define variables and bind arguments cannot be
BOOLEANs or index-by tables. The only exception is that a PL/SQL record can appear in the
You can execute a dynamic SQL statement repeatedly using new values for the bind arguments. You still incur some overhead, because
IMMEDIATE re-prepares the dynamic string before every execution.
The string argument to the
EXECUTE IMMEDIATE command cannot be one of the national character types, such as
Note:When using dynamic SQL with PL/SQL, be aware of the risks of SQL injection, which is a possible security issue. For more information on SQL injection and possible problems, see Oracle Database Application Developer's Guide - Fundamentals. You can also search for "SQL injection" on the Oracle Technology Network at
For examples, see the following: