The EXECUTE IMMEDIATE statement prepares an executable
form of an SQL statement from a character string form of the statement
and then executes the SQL statement. EXECUTE IMMEDIATE combines the
basic functions of the PREPARE and EXECUTE statements.
Invocation
This statement
can only be specified in a PL/SQL context.
Authorization
The authorization rules are
those defined for the specified SQL statement.
The authorization
ID of the statement might be affected by the DYNAMICRULES bind option.
Syntax
>>-EXECUTE IMMEDIATE--sql-expression---------------------------->
>--+---------------------------------------+-------------------->
| .-,--------. |
| V | |
+-INTO----variable-+--------------------+
| .-,--------------. |
| V | |
'-BULK COLLECT INTO----array-variable-+-'
>--+-----------------------------------+-----------------------><
| .-,----------------------. |
| V .-IN-. | |
'-USING----+-+----+--expression-+-+-'
+-IN OUT--variable---+
'-OUT--variable------'
Description
- sql-expression
- An expression returning the statement string to be executed. The
expression must return a character-string type that is less than the
maximum statement size of 2 097 152 bytes. Note that a CLOB(2097152)
can contain a maximum size statement, but a VARCHAR cannot.
The
statement string must be one of the following SQL statements:
- ALTER
- CALL
- COMMENT
- COMMIT
- Compound SQL (compiled)
- Compound SQL (inlined)
- CREATE
- DECLARE GLOBAL TEMPORARY TABLE
- DELETE
- DROP
- EXPLAIN
- FLUSH EVENT MONITOR
- FLUSH PACKAGE CACHE
- GRANT
- INSERT
- LOCK TABLE
- MERGE
- REFRESH TABLE
- RELEASE SAVEPOINT
- RENAME
- REVOKE
- ROLLBACK
- SAVEPOINT
- SELECT (only when the EXECUTE IMMEDIATE statement
also specifies the BULK COLLECT INTO clause)
- SET COMPILATION ENVIRONMENT
- SET CURRENT DECFLOAT ROUNDING MODE
- SET CURRENT DEFAULT TRANSFORM GROUP
- SET CURRENT DEGREE
- SET CURRENT FEDERATED ASYNCHRONY
- SET CURRENT EXPLAIN MODE
- SET CURRENT EXPLAIN SNAPSHOT
- SET CURRENT IMPLICIT XMLPARSE OPTION
- SET CURRENT ISOLATION
- SET CURRENT LOCALE LC_TIME
- SET CURRENT LOCK TIMEOUT
- SET CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION
- SET CURRENT MDC ROLLOUT MODE
- SET CURRENT OPTIMIZATION PROFILE
- SET CURRENT QUERY OPTIMIZATION
- SET CURRENT REFRESH AGE
- SET CURRENT SQL_CCFLAGS
- SET ROLE (only if DYNAMICRULES run behavior is in effect for the
package)
- SET ENCRYPTION PASSWORD
- SET EVENT MONITOR STATE (only if DYNAMICRULES run behavior is
in effect for the package)
- SET INTEGRITY
- SET PASSTHRU
- SET PATH
- SET SCHEMA
- SET SERVER OPTION
- SET SESSION AUTHORIZATION
- SET variable
- TRANSFER OWNERSHIP (only if DYNAMICRULES run behavior is in effect
for the package)
- TRUNCATE (only if DYNAMICRULES run behavior is in effect for the
package)
- UPDATE
The statement string must not contain a statement terminator,
with the exception of compound SQL statements which can contain semicolons
(;) to separate statements within the compound block. A compound SQL
statement is used within some CREATE and ALTER statements which, therefore,
can also contain semicolons.
When an EXECUTE IMMEDIATE statement
is executed, the specified statement string is parsed and checked
for errors. If the SQL statement is invalid, it is not executed, and
an exception is thrown.
- INTO variable
- Specifies the name of a variable that is to receive an output
value from the corresponding parameter marker.
- BULK COLLECT INTO array-variable
- Identifies one or more variables with an array data type. Each
row of the query is assigned to an element in each array in the order
of the result set, with the array index assigned in sequence.
- If exactly one array-variable is specified:
- If the data type of the array-variable element
is not a record type, the SELECT list must have exactly one column
and the column data type must be assignable to the array element data
type.
- If the data type of the array-variable element
is a record type, the SELECT list must be assignable to the record
type.
- If multiple array variables are specified:
- The data type of the array-variable element
must not be a record type.
- There must be an array-variable for each column
in the SELECT list.
- The data type of each column in the SELECT list must be assignable
to the array element data type of the corresponding array-variable.
If the data type of array-variable is an ordinary
array, the maximum cardinality must be greater than or equal to the
number of rows that are returned by the query.This clause can only
be used if the sql-expression is a SELECT statement.
- USING
- IN expression
- Specifies a value that is passed to an input parameter
marker. IN is the default.
- IN OUT variable
- Specifies the name of a variable that provides an input value
to, or receives an output value from the corresponding parameter marker.
This option is not supported when the INTO or BULK
COLLECT INTO clause is used.
- OUT variable
- Specifies the name of a variable that receives an output value
from the corresponding parameter marker. This option is not supported
when the INTO or BULK COLLECT INTO clause
is used.
The number and order of evaluated expressions or
variables must match the number and order of-and be type-compatible
with-the parameter markers in
sql-expression.
Notes
- Statement caching affects the behavior of an EXECUTE IMMEDIATE
statement.
Example
CREATE OR REPLACE PROCEDURE proc1( p1 IN NUMBER, p2 IN OUT NUMBER, p3 OUT NUMBER )
IS
BEGIN
p3 := p1 + 1;
p2 := p2 + 1;
END;
/
EXECUTE IMMEDIATE 'BEGIN proc1( :1, :2, :3 ); END' USING IN p1 + 10, IN OUT p3,
OUT p2;
EXECUTE IMMEDIATE 'BEGIN proc1( :1, :2, :3 ); END' INTO p3, p2 USING p1 + 10, p3;