OPEN CURSOR..FETCH NEXT CURSOR - dump

[Former Member]

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.

[Former Member]

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á

[Former Member]

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.

[Former Member]

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

[Former Member]

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

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

[kesavadas_thekkillath]

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.

[Former Member]

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<----

---

I

I

I--

---

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

I

I<----

---

I

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.

[former_member186741]

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.

[Former Member]

Change the declaration to static

STATICS : s_cursor TYPE cursor .

[Former Member]

Thank you for reply,

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

[Former Member]

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

Regards,

Nikhil

[kesavadas_thekkillath]

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

[Former Member]

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

[Former Member]

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:)

[Former Member]

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

[kesavadas_thekkillath]

I think BAPI is closed the cursor:)

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

[Former Member]

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

[SuhaSaha]

>

> 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]

[Former Member]

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.

[Former Member]

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

[kesavadas_thekkillath]

Hi,

Check what suhas has told.

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

Your dump message is also clear

One of the database selections included a database Commit. | | The selection was then supposed to continue.

So to confirm just comment the function call and execute, there wont be any dump.

@Suhas - Thanks for correcitng me

[Former Member]

In SAP documentation you can find the following:

The following events close a cursor:

- The CLOSE CURSOR statement.

- The Open SQL statement COMMIT WORK

- A database commit in Native SQL. In this case, a cursor opened with WITH HOLD is not closed.

- The Open SQL statement ROLLBACK WORK

- A database rollback in Native SQL

- A screen change, in particular the statements CALL SCREEN, CALL DIALOG, CALL TRANSACTION, MESSAGE

- A Remote Function Call, in particular the statements CALL FUNCTION ... DESTINATION , CALL FUNCTION ... STARTING NEW TASK , CALL FUNCTION ... IN BACKGROUND TASK and WAIT.

Maybe in the BAPI one of these events occurs.

[Former Member]

Maybe i should look another function to use instead of BAPI.

[Former Member]

Can you please provide the DUMP details.

[Former Member]

Runtime Errors DBIF_RSQL_INVALID_CURSOR

Except. CX_SY_OPEN_SQL_DB

Date and Time 23.07.2010 15:56:03

-

---

-

---

Short text

Invalid interruption of a database selection.

-

---

-

---

What happened?

Error in the ABAP Application Program

The current ABAP program "ZCE_004" had to be terminated because it has

come across a statement that unfortunately cannot be executed.

Unable to perform database selection fully.

-

---

-

---

What can you do?

If the error occurs in debugging mode, you should first run

the processes to be tested without debugging activated.

If the error persists outside debugging, you must check

the application program more closely.

No error that requires action

Note down which actions and inputs caused the error.

To process the problem further, contact you SAP system

administrator.

Using Transaction ST22 for ABAP Dump Analysis, you can look

at and manage termination messages, and you can also

keep them for a long time.

-

---

-

---

Error analysis

An exception occurred that is explained in detail below.

The exception, which is assigned to class 'CX_SY_OPEN_SQL_DB', was not caught

in

procedure "PROCESS" "(FORM)", nor was it propagated by a RAISING clause.

Since the caller of the procedure could not have anticipated that the

exception would occur, the current program is terminated.

The reason for the exception is:

One of the database selections included a database Commit.

The selection was then supposed to continue. Before a

database commit, however, all outstanding database selections

must be concluded.

Possible causes in the application program:

While a read process from a database cursor is taking place

(within a loop SELECT/LOOP/EXEC SQL or before a FETCH command),

one of the following statements is used:

- MESSAGE (apart from MESSAGE S...)

- COMMIT WORK

- ROLLBACK WORK

- BREAK-POINT

- WAIT

- CALL FUNCTION ... DESTINATION (synchronous RFC)

- CALL FUNCTION ... STARTING NEW TASK

- RECEIVE RESULTS

- CALL DIALOG

- CALL SELECTION-SCREEN

- CALL TRANSACTION

- CALL SCREEN, or any other statement that results in the display of a

new screen

Whenever a program runs in debugging mode, a "COMMIT WORK" can

possibly be triggered during database selection. This abnormal

termination can also occur in debugging mode even with a correct

program.

A "COMMIT WORK" during debugging may be due to the following reasons:

1. A program or screen was regenerated during debugging

and updated in the database.

2. Each user needs a separate process in debugging mode, but

the number of available processes is restricted. If this

limit is exceeded, each debugging step then requires a

"COMMIT WORK".

The error occurs in a statement in which the table "AFRU " is accessed.

-

---

[Former Member]

---

How to correct the error

1. If the error occurs in debugging:

_ Repeat the test that caused the error outside debugging mode.

By doing this, you regenerate all the programs and screens

needed for the run and update them in the database, if necessary.

You thereby eliminate one possible cause of the

error.

If the error persists, it could be that the number of

processes available for debugging has been exceeded.

This is often due to a temporary bottleneck caused by

too many users working in the debugging facility at the same time.

If this bottleneck occurs frequently, you should

check whether it is possible to make more processes

available.

2. If the error occurs outside debugging:

The problem cannot be resolved without correcting the application

program.

The statements MESSAGE, COMMIT WORK, ROLLBACK WORK, CALL SCREEN,

BREAK-POINT, WAIT, CALL FUNCTION ... DESTINATION, CALL FUNCTION ...

STARTING NEW TASK, RECEIVE RESULTS, CALL SELECTION-SCREEN, CALL

DIALOG, CALL TRANSACTION cannot be used within a database

loop.

You can collect the data needed for these statements together in an

internal table and process them

in a loop.

-

With SELECT loops, the statement

SELECT * FROM ... INTO TABLE ...

is often useful.

If the error occures in a non-modified SAP program, you may be able to

find an interim solution in an SAP Note.

If you have access to SAP Notes, carry out a search with the following

keywords:

"DBIF_RSQL_INVALID_CURSOR" "CX_SY_OPEN_SQL_DB"

"ZCE_004" or "ZCE_004"

"PROCESS"

The exception must either be prevented, caught within proedure

"PROCESS" "(FORM)", or its possible occurrence must be declared in the

RAISING clause of the procedure.

To prevent the exception, note the following:

-

---

[Former Member]

---

System environment

SAP-Release 701

Char.set.... "C"

SAP kernel....... 701

Patch level. 77

Patch text.. " "

Memory consumption

Roll.... 16192

EM...... 20949240

Heap.... 0

Page.... 98304

MM Used. 6718768

MM Free. 1658128

MM Used. 6718768

MM Free. 1658128

-

---

[Former Member]

If it is needed , i can post rest of dump informatin .

Thanks

[kesavadas_thekkillath]

Can you just try this

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.

      call function 'DB_COMMIT'.  "<----
 
    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.
      call function 'DB_COMMIT'.  "<----
    ENDLOOP.


  ENDDO.

[Former Member]

Thank you for reply,

I use DB_COMMIT . But still dump is occured .

[Former Member]

I know this is an old thread, but I just got the same problem, in my case the problem was the statement INTO TABLE, the order of the fields I was selecting was not the same as the order of the fields in the table, this was causing some type problems, once I got everything in the same order, it worked like a charm.

Hope this helps.

[Former Member]

Hi Former Member,

The short dump occurs if you have use "COMMIT WORK", or "COMMIT WORK AND WAIT" (in short if you introduce any type of COMMIT) in your program. Please remove the statment and it will get removed. Also, One point to be noted that, when you use OPEN CURSOR, the DB COMMIT automatically happens at the Data base level, so no need to write COMMIT WORK.