SQL Reference

EXECUTE IMMEDIATE

The EXECUTE IMMEDIATE statement:

• Prepares an executable form of an SQL statement from a character string form of the statement.
• 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 be embedded only in an application program. It is an executable statement that cannot be dynamically prepared.

Authorization

The authorization rules are those defined for the SQL statement specified by EXECUTE IMMEDIATE.

Syntax

>>-EXECUTE IMMEDIATE--host-variable----------------------------><
 

Description

host-variable

A host variable must be specified and it must identify a host variable that is described in the program in accordance with the rules for declaring character-string variables. It must be a character-string variable less than the maximum statement size of 65 535. Note that a CLOB(65535) can contain a maximum size statement but a VARCHAR can not.

The value of the identified host variable is called the statement string.

The statement string must be one of the following SQL statements:

• ALTER
• COMMENT ON
• COMMIT
• CREATE
• DELETE
• DECLARE GLOBAL TEMPORARY TABLE
• DROP
• GRANT
• INSERT
• LOCK TABLE
• REFRESH TABLE
• RELEASE SAVEPOINT
• RENAME TABLE
• RENAME TABLESPACE
• REVOKE
• ROLLBACK
• SAVEPOINT
• SET CURRENT DEGREE
• SET CURRENT EXPLAIN MODE
• SET CURRENT EXPLAIN SNAPSHOT
• SET CURRENT QUERY OPTIMIZATION
• SET CURRENT REFRESH AGE
• SET CURRENT TRANSFORM GROUP
• SET EVENT MONITOR STATE
• SET INTEGRITY
• SET PASSTHRU
• SET PATH
• SET SCHEMA
• SET SERVER OPTION
• 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 the CREATE TRIGGER statement which can contain a semi-colon (;) to separate triggered SQL statements, or the CREATE PROCEDURE statement to separate SQL statements in the SQL procedure body.

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. See Dynamic SQL Statement Caching for information.

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;