Unknown Result Column Data Types
For some dynamic SELECT statements, the program knows the data types of the resulting columns and, consequently, the data types of the result variables used to store the column values. If the program has this information, the program can use the EXECUTE IMMEDIATE statement with the into clause to execute the SELECT statement.
In the following example, a database contains several password tables, each having one column and one row and containing a password value. An application connected to this database requires a user to successfully enter two passwords before continuing. The first password entered is the name of a password table and the second is the password value in that table.
The following code uses the EXECUTE IMMEDIATE statement to execute the dynamically-defined select built by the application to check these passwords:
...
prompt for table_password and value_password
select_stmt = 'select column1 from ' +
table_password;
exec sql execute immediate :select_stmt
into :result_password;
if (sqlstate < 0) or (value_password <>
result_password) then
print 'Password authorization failure'
endif
...
Because the application developer knows the data type of the column in the password table (although not which password table is selected), the developer can execute the dynamic select with the EXECUTE IMMEDIATE statement and the INTO clause.
The syntax of EXECUTE IMMEDIATE in this context is:
exec sql execute immediate select_statement
into variable{,variable};
[exec sql begin;
host_code
exec sql end;]
This syntax retrieves the results of the select into the specified host language variables. The begin and end statements define a select loop that processes each row returned by the SELECT statement and terminates when there are no more rows to process. If a select loop is used, your program cannot issue any other SQL statements for the duration of the loop.
If the select loop is not included in the statement, the DBMS Server assumes that the SELECT statement is a singleton select returning only one row and, if more than one row is returned, issues an error.