The
EXECUTE IMMEDIATE statement prepares an executable form of an SQL
statement from a character string form of the statement, and executes
the SQL statement.
EXECUTE IMMEDIATE combines the basic functions of the
PREPARE and EXECUTE statements. It can be used to prepare and execute
SQL statements that contain neither host variables nor parameter markers.
Invocation
This statement can only be embedded
in an application program. It is an executable statement that cannot
be dynamically prepared.
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----expression-----------------------------><
Description
- 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
- SET COMPILATION ENVIRONMENT
- SET CURRENT DECFLOAT ROUNDING MODE
- SET CURRENT DEFAULT TRANSFORM GROUP
- SET CURRENT DEGREE
- SET CURRENT EXPLAIN MODE
- SET CURRENT EXPLAIN SNAPSHOT
- SET CURRENT FEDERATED ASYNCHRONY
- SET CURRENT IMPLICIT XMLPARSE OPTION
- SET CURRENT ISOLATION
- SET CURRENT LOCALE LC_MESSAGES
- 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 TEMPORAL BUSINESS_TIME
- SET CURRENT TEMPORAL SYSTEM_TIME
- 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 ROLE (only if DYNAMICRULES run behavior is in effect for the
package)
- SET SCHEMA
- SET SERVER OPTION
- SET SESSION AUTHORIZATION
- SET SQL_CCFLAGS
- SET USAGE LIST STATE (only if DYNAMICRULES run
behavior is in effect for the package)
- 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 include parameter markers
or references to host variables, and must not begin with EXEC SQL.
It must not contain a statement terminator, with the exception of
compound SQL statements which can contain semi-colons (;) to separate
statements within the compound block. A compound SQL statement is
used within some CREATE and ALTER statements which, therefore, can
also contain semi-colons.
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 the error condition that prevents
its execution is reported in the SQLCA. If the SQL statement is valid,
but an error occurs during its execution, that error condition is
reported in the SQLCA.
Notes
- Statement caching affects the behavior of an EXECUTE IMMEDIATE
statement.
Example
Use C program statements to move
an SQL statement to the host variable
qstring (char[80]),
and prepare and execute whatever SQL statement is in the host variable
qstring.
if ( strcmp(accounts,"BIG") == 0 )
strcpy (qstring,"INSERT INTO WORK_TABLE SELECT *
FROM EMP_ACT WHERE ACTNO < 100");
else
strcpy (qstring,"INSERT INTO WORK_TABLE SELECT *
FROM EMP_ACT WHERE ACTNO >= 100");
.
.
.
EXEC SQL EXECUTE IMMEDIATE :qstring;