PowerBuilder 12.5 sql cursors transaction size error

Question

i have a major problem and trying to find a workaround. I have an application in PB12.5 that works on both sql and oracle dbs.. (with a lot of data)

and i m using CURSOR at a point,, but the aplications crashes only in sql. Using debuging in PB i found that the sql connection returs -1 due to huge transaction size. But i want to fetch row by row my data.. is any work around to fetch data like paging?? i mean lets fetch the first 1000 rows next the other 1000 and so on.. i hope that you understand what i want to achieve (to break the fetch process and so to reduce the transaction size if possible) , here is my code

DECLARE trans_Curs CURSOR FOR 
 SELECT associate_trans.trans_code  
 FROM associate_trans  
WHERE associate_trans.usage_code = :ggs_vars.usage  ORDER BY associate_trans.trans_code ;

  OPEN trans_Curs;
  FETCH  trans_Curs INTO :ll_transId;

  DO WHILE sqlca.sqlcode = 0
  ll_index += 1
        hpb_1.Position = ll_index
        if not guo_associates.of_asstrans_updatemaster( ll_transId, ls_error) then
        ROLLBACK;
        CLOSE trans_Curs;
      SetPointer(Arrow!)
      MessageBox("Update Process", "Problem with the update process on~r~n" + sqlca.sqlerrtext)
    cb_2.Enabled = TRUE
    return
 end if
 FETCH trans_Curs INTO :ll_transId;
 LOOP
 CLOSE trans_Curs; 

Answer 1

First of all thank you FDavidov for your effort, so i managed to do it using dynamic datastore instead of cursor,, so here is my solution in case someone else need this.

    String ls_sql, ls_syntax, ls_err
    Long ll_row
    DataStore lds_info      

    ls_sql = "SELECT associate_trans.trans_code  "  &
                + " FROM associate_trans " &
                + "  WHERE  associate_trans.usage_code = '" + ggs_vars.usage +"' "&
                + " ORDER BY associate_trans.trans_code"
    ls_syntax = SQLCA.SyntaxFromSQL( ls_sql, "", ls_err )

    IF ls_err <> '' THEN
    MessageBox( 'Error...', ls_err )
    RETURN
    END IF

    lds_info = CREATE DataStore
    lds_info.Create( ls_syntax, ls_err )
    lds_info.SetTransObject( SQLCA )
    lds_info.Retrieve( )        

        DO WHILE sqlca.sqlcode = 0 and ll_row <= ll_count               
                FOR ll_row = 1 TO ll_count
                    ll_transId = lds_info.GetItemNumber( ll_row, 'trans_code' )                         
                        ll_index += 1
                        hpb_1.Position = ll_index
                        do while yield(); loop
                        if not guo_associates.of_asstrans_updatemaster( ll_transId, ls_error) then
                            ROLLBACK;
                            DESTROY lds_info
                            SetPointer(Arrow!)
                            MessageBox("Update Process", "Problem with the update process on~r~n" + sqlca.sqlerrtext)
                            cb_2.Enabled = TRUE
                            return
                        end if              
                    NEXT
                    DESTROY lds_info                    
        LOOP