DBSQL_NO_PERM_MEMORY

[Former Member]

Hi,

I have used the below select query in my program.

         SELECT knumh kappl kschl pltyp datbi datab matnr

            INTO CORRESPONDING FIELDS OF TABLE gt_dattrn_price

                 FROM a607

                 FOR ALL ENTRIES IN lit_cdhdr

                 WHERE knumh = lit_cdhdr-objectid

                   AND kappl = p_kappl

                   AND kschl IN s_kschl

                   AND pltyp IN r_pltyp

                   AND matnr IN s_matnr

                   AND datbi GE sy-datum

                   AND datab LE sy-datum.

There are some 25,000 records which are fetched from cdhdr table. On running the same program in background we received a dump

Internal session terminated with a runtime error DBSQL_ALLOCATION_FAILED (see ST22)

Runtime Errors         DBSQL_NO_PERM_MEMORY

Could you please give a solution to this.

[Former Member]

Hi,

I have used the below select query in my program.

         SELECT knumh kappl kschl pltyp datbi datab matnr

            INTO CORRESPONDING FIELDS OF TABLE gt_dattrn_price

                 FROM a607

                 FOR ALL ENTRIES IN lit_cdhdr

                 WHERE knumh = lit_cdhdr-objectid

                   AND kappl = p_kappl

                   AND kschl IN s_kschl

                   AND pltyp IN r_pltyp

                   AND matnr IN s_matnr

                   AND datbi GE sy-datum

                   AND datab LE sy-datum.

There are some 25,000 records which are fetched from cdhdr table. On running the same program in background we received a dump

Internal session terminated with a runtime error DBSQL_ALLOCATION_FAILED (see ST22)

Runtime Errors         DBSQL_NO_PERM_MEMORY

Could you please give a solution to this.

[Former Member]

This would be the place where you use the PACKAGE SIZE addition to the SELECT statement. You will be able to process your data in smaller chunks and should be able to get around this error.

Rob

[Former Member]

You mean I should use another select query than the above one. I am not sure if this would be the issue with the basis setting or the code

[Former Member]

It's doubtful if you will get Basis to change their settings so that a single program can run. You can use the PACKAGE SIZE option of the SELECT statement to retrieve and process smaller chunks of data at one time. Press F1 on SELECT and you'll find the documentation.

Rob

[Former Member]

have you tried to use a more complex SQL Statement, including a join instead of the "FOR ALL ENTRIES" ?

depending on the DB you are using this might solve the problem, as "FOR ALL ENTIRES" esentially creates a loop, and with a size of 25000 elements this is no easy task

[Former Member]

So you mean if I remove 'for all entries" and place a join the number of entries in CDHDR table would not matter, As I would also be receiving 1 lac entries in CDHDR table