6. Dynamic OpenSQL : How to Execute a Dynamic Nonselect Statement : Executing a Non-select Statement Using Execute Immediate
 
Share this page                  
Executing a Non-select Statement Using Execute Immediate
EXECUTE IMMEDIATE executes an OpenSQL statement specified using a string literal or host variable. The EXECUTE IMMEDIATE statement can be used to execute all but a few of the OpenSQL statements. For a list of statements that you cannot execute with the EXECUTE IMMEDIATE statement, see Execute Immediate Statement (see page Execute Immediate Statement).
When the EXECUTE IMMEDIATE statement is used to execute a statement that is not a select, its syntax is as follows:
EXEC SQL EXECUTE IMMEDIATE statement_string;
For example, the following statement executes a DROP statement specified as a string literal:
/*
** Statement specification included 
** in string literal. The string literal does 
** NOT include 'exec sql' or ';'
*/
exec sql execute immediate 'drop employee';
As another example, the following code reads OpenSQL statements from a file into a host string variable named buffer, and executes the contents of the variable. If the variable includes a statement that cannot be executed by EXECUTE IMMEDIATE, or if another error occurs, the loop is broken.
exec sql begin declare section;
    character buffer(100);
exec sql end declare section;
open file;
loop while not end of file and not error

read statement from file into buffer;
exec sql execute immediate :buffer;

end loop;
close file;
If only a statement's parameters, such as an employee name or number, change at runtime, then you do not need to use EXECUTE IMMEDIATE; you can replace a value with a host variable. For example, the following fragment increases the salaries of all employees with a specific employee number (read out of a file into variable, number):
loop while not end of file and not error

read number from file;
exec sql update employee
  set sal = sal * 1.1 
  where eno = :number;

end loop;