EXECUTE IMMEDIATE 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

   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;