09-18-2008 3:43 PM
Hello All,
I have the following code which short dumps after losing its cursor position.
OPEN CURSOR WITH HOLD dbcur FOR
select * from ekpo where bukrs in s_bukrs2.
do.
FETCH NEXT CURSOR dbcur INTO table iekpo package size pkgsize.
if sy-subrc <> 0.
close cursor dbcur.
exit.
else.
perform ouput_to_text_file.
refresh: iekpo.
endif.
enddo.
I have millions of records to be extracted and simple select to an internal table causes memory problems. Thus I need to extract a chunk of records write them to a text file, then get the next chunk.
Any suggestions?
Thank you,
Jerry
09-18-2008 3:48 PM
What's going on in "ouput_to_text_file"? Whatever it is that loses the cursor, maybe it can be replaced with something that does not lose it. e.g. COMMIT WORK can be replaced by a call to function DB_COMMIT. Subject to your analysis.
Another approach would be to SELECT UP TO x ROWS, remember the last primary key (EBELN + EBELP) and continue reading from that key onwards in each loop.
Thomas
09-18-2008 3:48 PM
What's going on in "ouput_to_text_file"? Whatever it is that loses the cursor, maybe it can be replaced with something that does not lose it. e.g. COMMIT WORK can be replaced by a call to function DB_COMMIT. Subject to your analysis.
Another approach would be to SELECT UP TO x ROWS, remember the last primary key (EBELN + EBELP) and continue reading from that key onwards in each loop.
Thomas
09-18-2008 3:58 PM
Thank you Thomas,
In the output to text file form I am using GUI Download to export data to a text file (or if in the background using dataset and transfer to write to the text file).
Your suggestion for reading up to x rows, how would that work exactly? Do you have an example you can share, specifically keeping track of what you've already selected and selecting the next records?
Thank you,
Jerry
09-18-2008 4:31 PM
GUI Download probably causes problem, writing to a dataset on app server might not cause the problem, maybe you want to try.
Regarding the select, here is some pseudo code outlining what I mean:
clear wa_ekpo.
do.
select * from ekpo into table lt_ekpo up to 10,000 rows
where ebeln > wa_ekpo-ebeln or ( ebeln = wa_ekpo-ebeln and ebelp > wa_ekpo-ebelp )
order by primary key.
if sy-subrc ne 0.
exit.
endif.
download lt_ekpo (appending to existing file)
l_lines = lines( lt_ekpo ).
read table lt_ekpo into wa_ekpo index l_lines.
enddo.
Will run for a while, but should not dump out
Thomas
09-18-2008 4:47 PM
That looks like it does the trick.
Thank you very much!
Points have been awarded!
Jerry