EXECUTE IMMEDIATE

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 variables nor parameter markers.

The EXECUTE IMMEDIATE statement:

Prepares an executable form of an SQL statement from a character string form of the statement
Executes the SQL statement

Invocation

This statement can only be embedded in an application program, SQL function, SQL procedure, or trigger. It is an executable statement that cannot be dynamically prepared. It must not be specified in Java™.

Authorization

Start of change If a global variable is referenced in a statement, the privileges held by the authorization ID of the statement must include at least one of the following: End of change

For the global variable identified in the statement,
- The READ privilege on the global variable, and
- The system authority *EXECUTE on the library containing the global variable
Administrative authority

The authorization rules are those defined for the SQL statement specified by EXECUTE IMMEDIATE. For example, see INSERT for the authorization rules that apply when an INSERT statement is executed using EXECUTE IMMEDIATE.

The authorization ID of the statement is the run-time authorization ID unless DYNUSRPRF(*OWNER) was specified on the CRTSQLxxx command when the program was created. For more information, see Authorization IDs and authorization names.

Syntax


>>-EXECUTE IMMEDIATE--+-variable----------+--------------------><
                      '-string-expression-'

Description

variable: Identifies a variable that must be declared in accordance with the rules for declaring character-string or Unicode graphic variables. An indicator variable must not be specified.
A global variable may only be used if the current connection is a local connection (not a DRDA connection).
string-expression: A string-expression is any PL/I string-expression that yields a character string. SQL expressions that yield a character string are not allowed. A string-expression is only allowed in PL/I.

The value of the identified variable or string expression is called a statement string.

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

ALTER	LABEL	SET CURRENT DECFLOAT ROUNDING MODE
CALL	LOCK TABLE	SET CURRENT DEGREE
COMMENT	MERGE	SET CURRENT IMPLICIT XMLPARSE OPTION
COMMIT	REFRESH TABLE	SET ENCRYPTION PASSWORD
CREATE	RELEASE SAVEPOINT	SET PATH
DECLARE GLOBAL TEMPORARY TABLE	RENAME	SET SCHEMA
DELETE	REVOKE	SET SESSION AUTHORIZATION
DROP	ROLLBACK	SET TRANSACTION
GRANT	SAVEPOINT	SET variable¹
INSERT	SET CURRENT DEBUG MODE	UPDATE

The statement string must not:

Begin with EXEC SQL.
End with END-EXEC or a semicolon.
Include references to variables.
Include parameter markers.

When an EXECUTE IMMEDIATE statement is executed, the specified statement string is parsed and checked for errors. If the SQL statement is not valid, it is not executed and the error condition that prevents its execution is reported in the stand-alone SQLSTATE and SQLCODE. If the SQL statement is valid, but an error occurs during its execution, that error condition is reported in the stand-alone SQLSTATE and SQLCODE. Additional information about the error can be retrieved from the SQL Diagnostics Area (or the SQLCA).

Note

Performance considerations: If the same SQL statement is to be executed more than once, it is more efficient to use the PREPARE and EXECUTE statements rather than the EXECUTE IMMEDIATE statement.

Example

Use C to execute the SQL statement in the variable Qstring.

void main () 
  {
   
    EXEC SQL  BEGIN DECLARE SECTION  END-EXEC.

    char Qstring[100] = "INSERT INTO WORK_TABLE SELECT * FROM EMPPROJACT 
     WHERE ACTNO >= 100"; 

    EXEC SQL  END DECLARE SECTION  END-EXEC.
    EXEC SQL  INCLUDE SQLCA;
  .
  .
  .
    EXEC SQL EXECUTE IMMEDIATE :Qstring;
  
    return;
  }

¹ The target of the SET variable statement must be a global variable.

² A select-statement is not allowed. To dynamically process a select-statement, use the PREPARE, DECLARE CURSOR, and OPEN statements.