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 end-exec

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 END-EXEC

               01      QUERY       PIC X(100).

               01      DATAVAL     PIC X(100).

               01      COUNTER     PIC S9(9) COMP.

               01      NUMCOLS     PIC S9(9) COMP.

               01      COLNAME     PIC X(32).

               01      COLTYPE     PIC S9(9) COMP.

               01      COLLEN      PIC S9(9) COMP.

          EXEC SQL END DECLARE SECTION END-EXEC.

...

          EXEC SQL WHENEVER SQLERROR PERFORM ERR-PARA END-EXEC.

          EXEC SQL WHENEVER SQLWARNING PERFORM WARN-PARA END-EXEC

          EXEC SQL WHENEVER NOT FOUND STOP END-EXEC.

...

        EXEC SQL USE pubs2 END-EXEC.

        MOVE "SELECT * FROM publishers " TO QUERY.

        EXEC SQL ALLOCATE DESCRIPTOR dout WITH MAX 100 END-EXEC.

        EXEC SQL PREPARE  dynstmt FROM :QUERY END-EXEC.

        EXEC SQL DECLARE dyncur CURSOR FOR dynstmt END-EXEC.

        EXEC SQL OPEN dyncur END-EXEC.

        PERFORM FETCH-LOOP UNTIL SQLCODE  = 100.

     * Clean-up all open cursors, descriptors and dynamic statements.

        EXEC SQL CLOSE dyncur END-EXEC.

        EXEC SQL DEALLOCATE CURSOR dyncur END-EXEC.

        EXEC SQL DEALLOCATE PREPARE dynstmt END-EXEC.

        EXEC SQL DEALLOCATE DESCRIPTOR dout END-EXEC.

        EXEC SQL COMMIT WORK END-EXEC.

        STOP RUN.

         FETCH-LOOP.

               EXEC SQL FETCH dyncur INTO SQL DESCRIPTOR dout END-EXEC

               EXEC SQL GET DESCRIPTOR dout :NUMCOLS = COUNT END-EXEC

               DISPLAY "COLS = ", NUMCOLS

               MOVE 1 TO COUNTER

               PERFORM GET-DESC-PARA UNTIL  COUNTER > NUMCOLS.

         END-FETCH-LOOP.

          GET-DESC-PARA.

                EXEC SQL GET DESCRIPTOR dout VALUE :COUNTER

                                 :COLNAME = NAME,

                                 :COLTYPE = TYPE,

                                 :COLLEN  = LENGTH

                              END-EXEC

                DISPLAY "NAME      :", COLNAME

                DISPLAY "TYPE      :", COLTYPE

                DISPLAY "LENGTH    :", COLLEN

                EXEC SQL GET DESCRIPTOR dout VALUE :COUNTER

                                 :DATAVAL = DATA END-EXEC

                DISPLAY "DATA      :", DATAVAL

                DISPLAY " "

                ADD 1 TO COUNTER.

         END-GET-DESC-PARA.

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 but rather should be included in the prepared statement‘s select query.