Package processing of mass data with database commit and SELECT statement

If you have to process a really huge number of records, you often face problems with memory or redo-log shortage, depending on which occurs first. A possible solution is to process data in packages.
There are two common questions regarding this case:
  1. How to store the current state of cursor of SELECT statement and restart it further?
  2. How to save the resulting changes and commit them in database?
The coding below shows how to deal with package processing and commit work in connection with SELECT statement.
Consider the following details:
  • Define a variable for database cursor using built-in ABAP type 'cursor'.
  • Specify your SELECT inside of OPEN CURSOR statement without INTO part.
  • The addition WITH HOLD will cause that the cursor isn't affected by database commit.
  • The INTO part of the SELECT statement is moved to the FETCH statement, you can choose the package processing here.
  • You don't have to initialize the internal table before the LOOP.
  • You have to use database commit CALL FUNCTION 'DB_COMMIT' instead of ABAP commit COMMIT WORK.
  • Unfortunately database commit doesn't reset the counter of runtime of program, this approach doesn't help to avoid limit caused by parameter rdisp/max_wprun_time.
DATA:
  cur      TYPE cursor,
  lt_usr02 TYPE TABLE OF usr02,
  ls_usr02 TYPE usr02.

OPEN CURSOR WITH HOLD cur FOR   
  SELECT * FROM usr02.          

DO.
  FETCH NEXT CURSOR cur
    INTO TABLE lt_usr02 PACKAGE SIZE 20.

  IF sy-subrc <> 0.
    EXIT.
  ENDIF.

  LOOP AT lt_usr02 INTO ls_usr02.  
    WRITE: / ls_usr02-bname.
  ENDLOOP.

  CALL FUNCTION 'DB_COMMIT'.   
ENDDO.

CLOSE CURSOR cur.
See other related notes in my infodepot:
Commit Work in SELECT statement Information about amount of read records returned by SELECT, SELECT SINGLE, SELECT COUNT, etc. SQL inner join vs. join of internal tables Select-Options in dynamic WHERE condition called per RFC Package processing of mass data with COMMIT WORK and SELECT statement
Full list of examples in my infodepot

If you have a question, have found an error or just want to contact me, please use this form.

Copyright (C) 2010 http://www.kerum.pl/infodepot/

Disclaimer: I am not affiliated or related to any division or subsidiary of SAP AG.
Trademarks or registered trademarks of any products or companies referred to on this site belong to those companies.
Anyone using the given solutions, is doing it under his/her own responsibility and at own risk.