How You Can Use the Execute Immediate Statement
You can use the execute immediate statement to perform any of the following tasks:
• Execute a variety of SQL statements that the 4GL code constructs at runtime.
• Execute SQL statements that contain legal SQL syntax that is not recognized by the OpenROAD compiler, such as:
– Creating a database procedure
– Issuing statements to a non-Ingres server or a version of the Ingres server that supports different SQL syntax
The syntax of the execute immediate statement is:
execute immediate statement_string [into variable {, variable}]
statement_string
Specifies a string constant, a varchar expression, or a StringObject reference variable value. It must adhere to the following restrictions:
• It must evaluate to a single SQL statement.
• It cannot contain an open or fetch statement or any of the exceptions for the execute immediate statement.
Assume, for example, that you want to create a temporary table with a unique name at runtime. Do the following to create the table:
1. Declare two variables, one to contain the table name (tname) and one to contain the I parameter (vstring):
tname = varchar(32);
vstring = varchar(500);
2. Build the temporary table name from the user's login, followed by numbers:
select :user_name = dbmsinfo('username');
tname = user_name + '123';
3. Load the create table statement into vstring and run it using the execute immediate statement:
vstring = 'create table ' + tname +
'(name varchar(50), addr varchar(100))';
execute immediate :vstring;
When the program runs the execute immediate statement in Step 3, it executes the statement contained in vstring, creating the specified table.
For more examples of using the execute immediate statement, see the Language Reference Guide online help.