topic Re: OPEN CURSOR..FETCH NEXT CURSOR - dump in Application Development and Automation Discussions

OPEN CURSOR..FETCH NEXT CURSOR - dump

Former Member — Thu, 22 Jul 2010 12:54:23 GMT

Hi,

A dump(CX_SY_OPEN_SQL_DB) is occured when i run the code below .

I think cursor is closed or lost because of BAPI_ALM_ORDER_GET_DETAIL.

I use OPEN CURSOR and FETCH NEXT CURSOR statements

because I use similar code below in a function which is called by RSA3.

If package size is 100, dump is occured at second fetching .

If package size is much less than 100,

for example 2,3,4 or 5,

dump is occured after several fetching .

any suggestion is appreciated .

Thanks .

DATA : s_cursor TYPE cursor .

DATA : lt_afru TYPE TABLE OF afru WITH HEADER LINE.

DATA : ls_header TYPE bapi_alm_order_header_e.

DATA : ls_srvdata TYPE bapi_alm_order_srvdat_e.

DATA : ls_reforder_item TYPE bapi_reforder_item.

DATA : lt_opr TYPE TABLE OF bapi_alm_order_operation_e.

DATA : lt_ret TYPE TABLE OF bapiret2 WITH HEADER LINE.

START-OF-SELECTION .

OPEN CURSOR WITH HOLD s_cursor FOR

SELECT * FROM afru .

DO.

REFRESH lt_afru .

FETCH NEXT CURSOR s_cursor

INTO TABLE lt_afru

PACKAGE SIZE 100.

LOOP AT lt_afru .

CALL FUNCTION 'BAPI_ALM_ORDER_GET_DETAIL'

EXPORTING

number = lt_afru-aufnr

IMPORTING

es_header = ls_header

es_srvdata = ls_srvdata

es_reforder_item = ls_reforder_item

TABLES

et_operations = lt_opr

return = lt_ret.

ENDLOOP.

ENDDO.

Re: OPEN CURSOR..FETCH NEXT CURSOR - dump

Former Member — Thu, 22 Jul 2010 13:01:26 GMT

Hi!

Why are you using native SQL? Use simply the OPEN SQL SELECT statement.

SELECT * FROM afru INTO TABLE lt_afru...

OR if you have to use the Native SQL...

SELECT the whole data, and only after selection, start the processing.

REFRESH lt_afru .

DO.
FETCH NEXT CURSOR s_cursor
INTO ls_afru.
APPEND ls_afru TO tl_afru.
ENDDO.

LOOP AT lt_afru .

CALL FUNCTION 'BAPI_ALM_ORDER_GET_DETAIL'
EXPORTING
number = lt_afru-aufnr
IMPORTING
es_header = ls_header
es_srvdata = ls_srvdata
es_reforder_item = ls_reforder_item
TABLES
et_operations = lt_opr
return = lt_ret.
ENDLOOP.

Regards

Tamá

Re: OPEN CURSOR..FETCH NEXT CURSOR - dump

Former Member — Thu, 22 Jul 2010 13:24:51 GMT

Thank you for the answer.

I can't get all data because a similar code is written in a function which will be used for BW transportation.

I test the function with using RSA3 .

So i need to get the data at smaller sizes.

This provide us not to have any memory problem and

Transported data stay in small sizes.

Re: OPEN CURSOR..FETCH NEXT CURSOR - dump

Former Member — Thu, 22 Jul 2010 13:42:14 GMT

I see.

Try replacing the DO...ENDDO statements with SELECT ... ENDSELECT.

DATA: lv_count TYPE i.
SELECT * FROM afru INTO ls_afru.
APPEND ls_afru TO lt_afru.
ADD 1 TO lv_count.
IF lv_count >= 100.
* call BAPI here
CLEAR lv_count.
REFRESH: lt_afru.
ENDIF.
ENDSELECT.

What do you think? Just for testing purposes...

Regards

Tamás

Edited by: Tamás Nyisztor on Jul 22, 2010 3:43 PM

Re: OPEN CURSOR..FETCH NEXT CURSOR - dump

Former Member — Thu, 22 Jul 2010 13:57:51 GMT

Hi,

Maybe i should write the problem more clearer .

The code i write above is just an example focused on cursor lost problem .

It shows the cursor is lost when BAPI_ALM_ORDER_GET_DETAIL is used between DO and ENDDO .

So why i need OPEN CURSOR--FETCH NEXT CURSOR instead of SELECT..ENDSELECT?

The function which will be used for BW tranportation is called much more than once .

When it is called first , a similar code below is run

OPEN CURSOR WITH HOLD s_cursor FOR

SELECT * FROM afru .

When it is called second time, this time fetch code is run in the same function

FETCH NEXT CURSOR s_cursor

INTO TABLE lt_afru

PACKAGE SIZE s_s_if-maxsize.

As you may know, RSA3 calls the function again and again until all data is get .

So we have no DO..ENDDO in our function, because it is called more than once

and everytime it is called fetching is done .

SELECT..ENDSELECT cant work here. Because we need a cursor to get the data.

In every time function is called, we always get the same data if we use SELECT..ENDSELECT.

Thanks

Edited by: Ceyhun Ergün on Jul 22, 2010 4:01 PM

Re: OPEN CURSOR..FETCH NEXT CURSOR - dump

kesavadas_thekkillath — Thu, 22 Jul 2010 15:09:13 GMT


SELECT * FROM afru .

is this a example or the actual code where you get the dump ?


SELECT..ENDSELECT cant work here. Because we need a cursor to get the data.
In every time function is called, we always get the same data if we use SELECT..ENDSELECT.

you can use the package size directly in query if required.

Re: OPEN CURSOR..FETCH NEXT CURSOR - dump

Former Member — Fri, 23 Jul 2010 06:20:18 GMT

Hi,

I try to tell my problem more clearer. This schema shows how zfunction is called.

RSA3

I--

> 1st call zfunction -> open cursor--

> ok

I<----

I--

> 2nd call zfunction -> fetch next cursor--> ok

I<----

I--

> 3nd call zfunction -> fetch next cursor--> dump occured!

DUMP is occured on the line of FETCH NEXT CURSOR .

The code i wrote in the first message, it simply simulates the problem.

Is select..endselect works for this situation?

zfunction code algorithm :

IF IsFunctionCalledFirst = true.

OPEN CURSOR...

ELSE.

FETCH NEXT CURSOR ... INTO TABLE itab.

LOOP AT itab.

CALL BAPI .

ENDLOOP.

ENDIF.

Re: OPEN CURSOR..FETCH NEXT CURSOR - dump

former_member186741 — Fri, 23 Jul 2010 06:33:38 GMT

I think you can just use the package size with a conventional select into table statement to achieve your goal. Basically the code is between a SELECT and ENDSELECT.

It would look something like this:

SELECT * FROM afru

into table lt_afru

package size 100.

LOOP AT lt_afru .

CALL FUNCTION 'BAPI_ALM_ORDER_GET_DETAIL'

EXPORTING

number = lt_afru-aufnr

IMPORTING

es_header = ls_header

es_srvdata = ls_srvdata

es_reforder_item = ls_reforder_item

TABLES

et_operations = lt_opr

return = lt_ret.

ENDLOOP.

endselect.

Re: OPEN CURSOR..FETCH NEXT CURSOR - dump

Former Member — Fri, 23 Jul 2010 07:02:38 GMT

Change the declaration to static

STATICS : s_cursor TYPE cursor .

Re: OPEN CURSOR..FETCH NEXT CURSOR - dump

Former Member — Fri, 23 Jul 2010 07:51:09 GMT

Thank you for reply,

I change declaration to statics. But still dump is occured .

Re: OPEN CURSOR..FETCH NEXT CURSOR - dump

Former Member — Fri, 23 Jul 2010 08:19:17 GMT

Please let me know the dump details and the values of each variable before dump using debugger.

Regards,

Nikhil

Re: OPEN CURSOR..FETCH NEXT CURSOR - dump

kesavadas_thekkillath — Fri, 23 Jul 2010 08:51:13 GMT

I hope your cursor is not getting closed before the second fetch

Re: OPEN CURSOR..FETCH NEXT CURSOR - dump

Former Member — Fri, 23 Jul 2010 08:59:29 GMT

you dont need to close the cursor every time.

Cursor should open in the first call and should close in the last call to this FM.

You need to check whether cursor value is clearing in between or not?

Whether cursor is closing in between?

Check it in debugger and see the cursor values in each call. After fetching all the records, check if it coming out correctly or not.

Also check cursor is closing after the select query is not fetching any data.

Regards,

Nikhil

Re: OPEN CURSOR..FETCH NEXT CURSOR - dump

Former Member — Fri, 23 Jul 2010 10:54:22 GMT

No, i don't close cursor before second fetch:)

FETCH NEXT CURSOR s_cursor

INTO TABLE lt_afru

PACKAGE SIZE 100.

if sy-subrc <> 0 .

CLOSE CURSOR s_cursor.

exit .

endif.

I am sorry that , i forgot to write this part of code in my first message:)

I think BAPI is closed the cursor:)

Re: OPEN CURSOR..FETCH NEXT CURSOR - dump

Former Member — Fri, 23 Jul 2010 11:05:12 GMT

Code should be

if sy-subrc NE 0 .

CLOSE CURSOR s_cursor.

exit .

endif.

Edited by: Nikhil V Kumar on Jul 23, 2010 4:49 PM

Re: OPEN CURSOR..FETCH NEXT CURSOR - dump

kesavadas_thekkillath — Fri, 23 Jul 2010 11:09:09 GMT


I think BAPI is closed the cursor:)

You are using with hold , so the cursor will not be closed due to commit.

Re: OPEN CURSOR..FETCH NEXT CURSOR - dump

Former Member — Fri, 23 Jul 2010 11:26:04 GMT

Nikhil V Kumar ,

it's exactly how you wrote.

i use "NE" now .

I think tags are invisible in this forum:)

Keshav.T ,

i know it must work , unfortunately it doesnt work.

I think BAPI close the cursor.

The code below is the final code:)

When you run this code, a dump is occured.

If you would like to see,

just run the code below .

REPORT ZCE_004.

START-OF-SELECTION .

PERFORM process .

*&----

*& Form PROCESS

*&----

FORM PROCESS .

STATICS : s_cursor TYPE cursor .

DATA : lt_afru TYPE TABLE OF afru WITH HEADER LINE.

DATA : ls_header TYPE bapi_alm_order_header_e.

DATA : ls_srvdata TYPE bapi_alm_order_srvdat_e.

DATA : ls_reforder_item TYPE bapi_reforder_item.

DATA : lt_opr TYPE TABLE OF bapi_alm_order_operation_e.

DATA : lt_ret TYPE TABLE OF bapiret2 WITH HEADER LINE.

OPEN CURSOR WITH HOLD s_cursor FOR

SELECT * FROM afru .

DO.

REFRESH lt_afru .

FETCH NEXT CURSOR s_cursor

INTO TABLE lt_afru

PACKAGE SIZE 100.

if sy-subrc NE 0 .

CLOSE CURSOR s_cursor.

exit .

endif.

LOOP AT lt_afru .

CALL FUNCTION 'BAPI_ALM_ORDER_GET_DETAIL'

EXPORTING

number = lt_afru-aufnr

IMPORTING

es_header = ls_header

es_srvdata = ls_srvdata

es_reforder_item = ls_reforder_item

TABLES

et_operations = lt_opr

return = lt_ret.

ENDLOOP.

ENDDO.

ENDFORM. " PROCESS

Edited by: Ceyhun Ergün on Jul 23, 2010 1:28 PM

Re: OPEN CURSOR..FETCH NEXT CURSOR - dump

SuhaSaha — Fri, 23 Jul 2010 11:52:58 GMT


> You are using with hold , so the cursor will not be closed due to commit.

Not exactly correct. SAP documentation says:

If the addition WITH HOLD is specified, the database cursor is not closed by a database commit executed using Native SQL. The addition does not have an influence, however, on implicit database commits or on any rollbacks which always close the database cursor.

Source: [http://help.sap.com/abapdocu_70/en/ABAPOPEN_CURSOR.htm]

Re: OPEN CURSOR..FETCH NEXT CURSOR - dump

Former Member — Fri, 23 Jul 2010 12:27:03 GMT

I executed here. I dont have any data in the table AFRU. But I added in the internal table by debugging. I didnt get any dump here.


START-OF-SELECTION .


  PERFORM process .


*&---------------------------------------------------------------------*
*&      Form  PROCESS
*&---------------------------------------------------------------------*
FORM PROCESS .

  STATICS : s_cursor TYPE cursor .
  DATA : lt_afru TYPE TABLE OF afru WITH HEADER LINE.
  DATA : ls_header        TYPE bapi_alm_order_header_e.
  DATA : ls_srvdata       TYPE bapi_alm_order_srvdat_e.
*  DATA : ls_reforder_item TYPE bapi_reforder_item.
  DATA : lt_opr TYPE TABLE OF  bapi_alm_order_operation_e.
  DATA : lt_ret TYPE TABLE OF bapiret2 WITH HEADER LINE.

  OPEN CURSOR WITH HOLD s_cursor FOR
  SELECT * FROM afru .

  DO.
    REFRESH lt_afru .
    FETCH NEXT CURSOR s_cursor
      INTO TABLE lt_afru
       PACKAGE SIZE  1.
    if sy-subrc NE 0 .
      CLOSE CURSOR s_cursor.
      exit .
    endif.

    LOOP AT lt_afru .

      CALL FUNCTION 'BAPI_ALM_ORDER_GET_DETAIL'
        EXPORTING
          number           = lt_afru-aufnr
        IMPORTING
          es_header        = ls_header
          es_srvdata       = ls_srvdata
*          es_reforder_item = ls_reforder_item
        TABLES
          et_operations    = lt_opr
          return           = lt_ret.
    ENDLOOP.
  ENDDO.

Re: OPEN CURSOR..FETCH NEXT CURSOR - dump

Former Member — Fri, 23 Jul 2010 13:07:43 GMT

First of all , thank you very very much for your reply and help.

As i said before, if package size is 100, the dump occured at the second fetch,

if package size is much less than 100, the dump occured after several fetch

I add a counter into my code,

I see this strange result :

Package Size/ fetch number dump occured

1 / 17

2 / 9

3 / 7

4 / 5

5 / 5

6 / 4

7 / 4

8 / 3

9 / 3

10 / 3

15 / 3

30 / 2

100 / 2

I tested this several times. And the result is always same.

The number of records in itab affects when the dump wil be occured .

strange.

Edited by: Ceyhun Ergün on Jul 23, 2010 3:07 PM