You use the EXECUTE IMMEDIATE statement to prepare and execute a dynamic statement in one step. In other words, EXECUTE IMMEDIATE corresponds to a PREPARE statement immediately followed by an EXECUTE statement. The statement does not, however, remain prepared and cannot be executed again with EXECUTE.
Dynamic CALL statements can be executed with EXECUTE IMMEDIATE if the procedure to be called has no procedure parameters or only procedure parameters of the type IN.
EXECUTE IMMEDIATE statement_variable
statement_variable ::= : host_variable
statement_variable
Alphanumeric host variable containing the statement text. The host variable can also be of the type CHAR(n), where 256 <= n <= 32000.
The following conditions must be satisfied:
The statement text cannot include any host variables or question marks as placeholders for unknown values.
The statement text cannot contain either SQL comments or comments in the host language. Pragmas (--%PRAGMA) are exceptions.
The statement text cannot be a SELECT statement or cursor description.
The RETURN INTO clause cannot be specified in an INSERT statement.
If the statement text contains a cursor name (DELETE WHERE CURRENT OF, UPDATE WHERE CURRENT OF), the cursor description for this cursor must be prepared and the cursor opened before the EXECUTE IMMEDIATE statement is executed.
Statements for EXECUTE IMMEDIATE
The following statements can be executed with EXECUTE IMMEDIATE:
ALTER SPACE ALTER STOGROUP ALTER TABLE COMMIT CALL (only input parameters; Type IN) CREATE INDEX CREATE FUNCTION CREATE PROCEDURE CREATE SCHEMA CREATE SPACE CREATE STOGROUP CREATE SYSTEM_USER CREATE TABLE CREATE USER CREATE VIEW DELETE DROP FUNCTION DROP INDEX DROP PROCEDURE | DROP SCHEMA DROP SPACE DROP STOGROUP DROP SYSTEM_USER DROP TABLE DROP USER DROP VIEW GRANT INSERT (without RETURN INTO clause) MERGE PERMIT REORG STATISTICS REVOKE ROLLBACK SET CATALOG SET SCHEMA SET SESSION AUTHORIZATION SET TRANSACTION UPDATE |
In addition, all utility statements can be executed with EXECUTE IMMEDIATE (see the “ SQL Reference Manual Part 2: Utilities”).
The following statements cannot be executed with EXECUTE IMMEDIATE:
ALLOCATE DESCRIPTOR CLOSE DEALLOCATE DESCRIPTOR DECLARE CURSOR DESCRIBE EXECUTE EXECUTE IMMEDIATE FETCH GET DESCRIPTOR | INCLUDE OPEN PREPARE RESTORE SELECT SET DESCRIPTOR STORE WHENEVER |
Example
An SQL statement is to be compiled and executed at runtime with EXECUTE IMMEDIATE:
The following SQL statement is read into SOURCESTMT as an alphanumeric string:
CREATE TABLE ordercust.orderproc.ordstat
(order_stat_num INTEGER, order_stat_text CHAR(15))
The statement is compiled and executed with:
EXEC SQL EXECUTE IMMEDIATE :SOURCESTMT END-EXEC
See also
EXECUTE, PREPARE