DB2 Version 10.1 for Linux, UNIX, and Windows

EXECUTE IMMEDIATE statement (PL/SQL)

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;