declare cursor (dynamic)

Declares a cursor for processing multiple rows returned by a prepared dynamic select statement.

exec sql [at connection_name] 
 declare cursor_name 
 cursor for prepped_statement_name;

cursor_name: The cursor’s name, used to reference the cursor in open, fetch, and close statements. A cursor’s name must be unique on each connection and must have no more than 255 characters.

prepped_statement_name: The name (specified in a previous prepare statement) that represents the select statement to be executed.

exec sql begin declare section;

CS_CHAR        sqlstmt[100];

exec sql end declare section;

strcpy(sqlstmt, “select * from publishers”);

exec sql prepare make_work from :sqlstmt;

exec sql declare make_work_cursor cursor for

     make_work;

exec sql deallocate prepare make_work;

The prepped_statement_name must not have a compute clause.
The cursor_name must be declared on the connection where prepped_statement_name was prepared.
The dynamic declare cursor statement is an executable statement, whereas the static declare cursor statement is simply a declaration. The dynamic declare statement must be located where the host language allows executable statements and the program should check return codes (SQLCODE, SQLCA, or SQLSTATE).
The for update and read only clauses for a dynamic cursor are not part of the declare cursor statement; however, they should be included in the prepared statement‘s select query.