topic Re: Problem with cursor! in Application Development and Automation Discussions

Problem with cursor!

Former Member — Tue, 13 Mar 2012 02:52:24 GMT

Hi Expert,

Is the below cursor does not working to handle huge data?

How to modified the cursor to be able to handle huge data? If the data cannot be retrieved in the first fetch, then it should move to 2nd fetch, 3rd fetch.. until all the data from huge table is successfully fetched.

DATA: source_data TYPE REF TO data,
      cursor_source TYPE cursor.
  FIELD-SYMBOLS: <fs_source> TYPE STANDARD TABLE.
  CREATE DATA source_data TYPE STANDARD TABLE OF DD02L.
  ASSIGN source_data->* TO <fs_source>.
  OPEN CURSOR WITH HOLD cursor_source FOR SELECT * FROM DD02L.
  IF sy-subrc = 0.
    FETCH NEXT CURSOR cursor_source INTO TABLE <fs_source>. 
  ELSEIF sy-subrc <> 0.
    EXIT.
  ENDIF.
  CLOSE CURSOR cursor_source.
  IF sy-subrc = 0.
    MODIFY ztest1234 FROM TABLE <fs_source>.
    COMMIT WORK.
    WRITE:/ 'Data successfully transferred into target table.'.
    WRITE:/'Total',sy-dbcnt,'row(s) affected.'.
  ENDIF.

<Added code formatting>

Message was edited by: Suhas Saha

Re: Problem with cursor!

kesavadas_thekkillath — Wed, 14 Mar 2012 08:47:26 GMT

Well, I see this thread not locked for a while as this concept can be easily taken from SAP help.

I would like to help you in this case. Refer the example below

data:cursor_source type cursor,
     it_mara type table of mara,
     it_out type table of mara.
open cursor with hold cursor_source for select * from mara.
if sy-subrc eq 0.
  do.
    fetch next cursor cursor_source into table it_mara
                                    package size 500.  "<--
    if sy-subrc ne 0.
      close cursor cursor_source.
      exit.
    endif.
    append lines of it_mara to it_out.
  enddo.
endif.

<Added code formatting>

Message was edited by: Suhas Saha

Re: Problem with cursor!

Former Member — Thu, 15 Mar 2012 02:18:14 GMT

I would like to modify the target table after every fetch to avoid out of memory exception if the table is too huge. But yet the below code is still wrong. do you have any idea? Thanks.

DATA: source_data TYPE REF TO data,
          cursor_source_data TYPE cursor.

   FIELD-SYMBOLS: <ft_source> TYPE STANDARD TABLE.

   CREATE DATA source_data TYPE STANDARD TABLE OF (p_source).
   ASSIGN source_data->* TO <ft_source>.

   OPEN CURSOR WITH HOLD cursor_source_data FOR SELECT * FROM (p_source) BYPASSING BUFFER.

   DO.
     FETCH NEXT CURSOR cursor_source_data APPENDING TABLE <ft_source> PACKAGE SIZE 5000.

     IF sy-subrc = 0.
       MODIFY (p_target) FROM TABLE <ft_source>.
     ELSEIF sy-subrc <> 0.
       EXIT.
     ENDIF.
   ENDDO.

   COMMIT WORK.
   CLOSE CURSOR cursor_source_data.

   WRITE:/ 'Data successfully copy from source table into target table.'.

<Added code formatting>

Message was edited by: Suhas Saha

Re: Problem with cursor!

Former Member — Thu, 15 Mar 2012 02:45:18 GMT

Hi,

Is below code correct now? able to handle very huge data and able to avoid AS ABAP stack for large data sets?

DATA: source_data TYPE REF TO data,
      cursor_source_data TYPE cursor.
  FIELD-SYMBOLS: <ft_source> TYPE STANDARD TABLE.
  CREATE DATA source_data TYPE STANDARD TABLE OF (p_source).
  ASSIGN source_data->* TO <ft_source>.
  OPEN CURSOR WITH HOLD cursor_source_data FOR SELECT * FROM (p_source) BYPASSING BUFFER.
  DO.
    FETCH NEXT CURSOR cursor_source_data APPENDING TABLE <ft_source> PACKAGE SIZE 5000.
    IF sy-subrc = 0.
      MODIFY (p_target) FROM TABLE <ft_source>.
      EXEC SQL.
        COMMIT WORK
      ENDEXEC.
    ELSEIF sy-subrc <> 0.
      CLOSE CURSOR cursor_source_data.
      EXIT.
    ENDIF.
  ENDDO.
  WRITE:/ 'Data successfully copy from source table into target table.'.

<Added code formatting>

Message was edited by: Suhas Saha

Re: Problem with cursor!

kesavadas_thekkillath — Thu, 15 Mar 2012 06:38:18 GMT

Hi,

I did some changes to your code, please have a look at it

data: source_data type ref to data,
      error_data type ref to data,
      cursor_source_data type cursor.
data: lc_error type ref to cx_sy_dynamic_osql_semantics,
      error_msg type string.
field-symbols: <ft_source> type standard table.
field-symbols: <ft_error>  type standard table.
create data source_data type standard table of (p_source).
create data error_data type standard table of (p_source).
if source_data is bound and error_data is bound.
  assign source_data->* to <ft_source>.
  assign error_data->* to <ft_error>.
  check <ft_source> is assigned and <ft_error> is assigned.
endif.
try .
open cursor with hold cursor_source_data for select * from (p_source)
                                             bypassing buffer.
catch cx_sy_dynamic_osql_semantics into lc_error.
  error_msg = lc_error->get_text( ).
  write error_msg.
  exit.
  endtry.
  do.
    fetch next cursor cursor_source_data appending table <ft_source>
                                          package size 5000.
    if sy-subrc eq 0.
      try .
      modify (p_target) from table <ft_source>.
      if sy-subrc = 0.
        commit work.
      else.
        rollback work.
        append lines of <ft_source> to <ft_error>."Collect it to error table.
      endif.
        catch cx_sy_dynamic_osql_semantics into lc_error.
          error_msg = lc_error->get_text( ).
       endtry.
    elseif sy-subrc ne 0.
      close cursor cursor_source_data.
      exit.
    endif.
enddo.

<Added code tags>

Message was edited by: Suhas Saha

Re: Problem with cursor!

SuhaSaha — Thu, 15 Mar 2012 07:57:28 GMT

Hello Kelvin,

What do you mean by "code is still worng"? Please be more specific about your problem if you need better responses!

One more question, why are you using APPENDING TABLE with FETCH CURSOR - can you not use INTO TABLE?

BR,

Suhas

Re: Problem with cursor!

Former Member — Thu, 15 Mar 2012 10:59:27 GMT

Hi Suhas,

Basically the above code that I programmed is working, but I am handling with huge data set. So, i am worry about out of memory exception.

I just want to make sure that my code is able to handle and avoid the out of memory exception.

This is the reason why I am writing the data into target table for every fetched.

For your opinion, is the above code able to avoid out of memory exception?

Thanks.

Re: Problem with cursor!

Former Member — Thu, 15 Mar 2012 11:19:06 GMT

Re: Problem with cursor!

Hi Kesavadas,

Thanks for your code.

Re: Problem with cursor!

kesavadas_thekkillath — Thu, 15 Mar 2012 11:45:16 GMT

Yes it will work, but note the point Suhas mentioned about "INTO TABLE" instead of "APPENDING TABLE". Was my mistake that I did not notice it .

Additionally you have to use "OPEN CURSOR WITH HOLD" becuase the commit or rollback will close the cursor. Also do not execute the program in foreground because there will be a time limit set for execution. Try to execute it in background.

Re: Problem with cursor!

Former Member — Thu, 15 Mar 2012 18:20:16 GMT

Hi Kesavadas,

INTO TABLE working perfect now.

My issue is solved.

Thank you so much Kesavadas and Suhas.