EXECUTE IMMEDIATE statement (Preprocess and execute SQL)

Function

The EXECUTE IMMEDIATE statement preprocesses and executes an SQL provided in a character string.

Privileges

None.

Format

EXECUTE IMMEDIATE {'character-string'|:embedded-variable}
[ {INTO :embedded-variable [:indicator-variable]
   [, : embedded-variable[: indicator-variable]]...
 |INTO DESCRIPTOR [:]SQL-descriptor-area-name}]
[{USING : embedded-variable[: indicator-variable]
     [, : embedded-variable[: indicator-variable]]...
 |USING DESCRIPTOR [:]SQL-descriptor-area-name}]

Operands

character-string
Specifies directly as a character literal the character string representing the SQL to be executed.
An apostrophe within the character literal specification of an SQL to be executed must be specified as two apostrophes in succession.
embedded-variable
Specifies as an embedded variable the character string of the SQL to be executed.
An embedded variable must be preceded by a colon (:).
embedded-variable
When retrieval results from a single-row SELECT statement are to be received or the CALL statement contains an output ? parameter and its value is to be received into embedded-variable, specifies the embedded variable that receives the value of the column of retrieval results or the value of the output ? parameter.
indicator-variable
Specifies an indicator variable to which a value indicating whether the value of a column of retrieval results returned to the embedded variable is the null value.
The indicator variable should be declared in the embedded SQL declaration section as an embedded variable having a SMALLINT data type.
If indicator-variable is omitted, the null value cannot be received.
SQL-descriptor-area-name
If retrieval results from a single-row SELECT statement are to be received or the CALL statement contains an output ? parameter and its value is to be received into the SQL descriptor area, specifies the SQL descriptor area name for receiving the value of the column of retrieval results or the value of the output ? parameter.
embedded-variable
If the SQL statement includes an input ? parameter and if its value is given by an embedded variable, specifies the embedded variable that contains the value to be assigned to the input ? parameter.
The number of embedded variables specified in the USING clause must be equal to the number of input ? parameters included in the SQL statement that is executed by the EXECUTE IMMEDIATE statement. Notice that the embedded variables and the input ? parameters are placed in correspondence with each other from the beginning in the order in which they are listed.
The embedded variable specified in the USING clause must have a data type that is allowed for the corresponding input ? parameter.
indicator-variable
Specifies an indicator variable that indicates whether the value of the embedded variable is the null value.
The indicator variable should be declared in the embedded SQL declaration section as an embedded variable having a SMALLINT data type.
If indicator-variable is omitted, the value of the indicator variable is assumed to be a non-null value.
SQL-descriptor-area-name
If the SQL statement includes an input ? parameter and its value is to be assigned from a descriptor area, specifies the name of the descriptor area that stores information on the input ? parameter.

Common rules

  1. Neither the SQL prefix nor the SQL terminator can be specified in the SQL character string that is to be executed.
  2. The maximum length of an SQL to be executed is 2,000,000 bytes. If the SQL character string is specified as a character literal, the maximum length is the maximum length of character literals in the UAP description language.
  3. The type of an embedded variable is the following structure:

    struct {
      long  xxxxxxx;  /* Effective length of SQL statement */
      char  yyyyyyy[n]; /* SQL statement storage area */
    } zzzzzzz;

    The characters xxxxxxx indicate the effective length of the character string stored in character array yyyyyyy.
    1 [Figure] (value of xxxxxxx) [Figure] 2000000
    The effective length of a character string does not include the characters \0 that indicate the end of the character string.
    n is any value.
  4. The number of embedded variables specified in the INTO clause should be equal to the number of retrieval result columns. If the SQL statement being executed is a single-row SELECT statement, and if the number of embedded variables and the number of columns are not equal, a warning flag W is assigned to the SQLWARN3 area of the SQL communications area. Notice that the embedded variables, the columns of retrieval results, or the embedded variables and the output ? parameters, are placed in correspondence from the beginning in the order in which they are listed.
  5. The embedded variable specified in the INTO clause should have a data type that is identical to the data type of the corresponding column or the output parameter, or a convertible data type.
  6. If the data fetched into an embedded variable of a fixed-length character string (including national character strings and mixed character strings) specified in the INTO clause is shorter than the definition length of the embedded variable, the data is inserted left-justified, and the remaining data is blank-filled.
  7. If the value of the retrieval result column or the value of the output ? parameter of the CALL statement is the null value, the value of the corresponding embedded variable may be unpredictable.

Notes

The EXECUTE IMMEDIATE statement produces the same result as executing the SQL shown below:

When an SQL specified in a character string is to be executed multiple times, the PREPARE statement should be used to preprocess the SQL and then the EXECUTE statement should be used to execute it repeatedly.

The following SQLs can be preprocessed and executed using the EXECUTE IMMEDIATE statement:

Examples

  1. Preprocess and execute SQL 'PURGE TABLE STOCK' that is provided in a character string:

    EXECUTE IMMEDIATE 'PURGE TABLE STOCK'

  2. Preprocess and execute an SQL statement that is defined as an embedded variable (:STOCKX):

    EXECUTE IMMEDIATE :STOCKX

  3. Preprocess SQL 'SELECT PNAME FROM STOCK WHERE PCODE = ?' given as a character string, and read the retrieval results into the embedded variable (:XPNAME) and indicator variable (:IPNAME). In this operation, specify the embedded variable (:XPCODE) and the indicator variable (:IPCODE) that store information on the value to be assigned to the ? parameter.

    EXECUTE IMMEDIATE 'SELECT PNAME FROM STOCK WHERE PCODE = ?'
         INTO :XPNAME:IPNAME
         USING :XPCODE:IPCODE