topic Re: Maximum size of an SQL statement in Application Development and Automation Discussions

Maximum size of an SQL statement

Former Member — Fri, 12 Jun 2009 10:15:24 GMT

Hello,

we get a short dump with runtime error DBIF_RSQL_SQL_ERROR and exception CX_SY_OPEN_SQL_DB.

The dump occurs at the following select statement:

select * from /rtc/tm_inuse

appending table gt_zrtc4inuse

where obj_name in lv_sel_copy

and trkorr ne p_trkorr.

We think the problem is the number of entries in the range table lv_sel_copy so that the maximum size of the SQL statement is reached.

But how likely is the maximum size ?

Depends the size on the data base ? We are using MaxDB 7.6, MaxDB 7.7.

How can we determine the maximum size so that we can calculate the nubmer of entries in the range tabel.

Any other idea or solution ?

Thanks

Arnfried

Re: Maximum size of an SQL statement

Former Member — Fri, 12 Jun 2009 10:22:16 GMT

Hi,

You are getting this dump because maybe your entries are huge and it might have exceeded the buffer space of the table. You have to ask your basis team to increase the size of the buffer space.

or,

You can segregate the range values into smaller ranges and fetch from the database accordingly..

You can see the size of the table space in DB02.

or please refer this thread:

Edited by: sneha singhania on Jun 12, 2009 4:01 PM

Re: Maximum size of an SQL statement

kesavadas_thekkillath — Fri, 12 Jun 2009 10:29:11 GMT

use 'package size' in your query

Re: Maximum size of an SQL statement

Former Member — Fri, 12 Jun 2009 10:29:22 GMT

Communicate with the basis team to increase the buffer size

Regards

Renu

Re: Maximum size of an SQL statement

Former Member — Fri, 12 Jun 2009 10:33:18 GMT

Hi,

I used 'package size' without success.

Edited by: Arnfried Dötsch on Jun 12, 2009 12:33 PM

Re: Maximum size of an SQL statement

Former Member — Fri, 12 Jun 2009 10:37:26 GMT

Hi,

You can see the size of the table space in DB02.

or please refer this thread:

Please try these..

Re: Maximum size of an SQL statement

Former Member — Fri, 12 Jun 2009 10:44:37 GMT

which is the parameter to increase the buffer size ?

Where can a see if the buffer size is to small ? (SysLog, ST02,...)

Re: Maximum size of an SQL statement

ThomasZloch — Fri, 12 Jun 2009 10:56:39 GMT

This has nothing to do with buffer size or DB02. As you suggested, your SQL statement grew to big due to many entries in the selection range. I don't know where the exact limit in KByte is quoted, probably somewhere in the MaxDB documentation. There is dedicated forums for this here on SCN. With Oracle, the problem occurs from 2000 or 3000 single values upwards, depending on circumstances.

A workaround could be to use a select statement with FOR ALL ENTRIES and an internal table rather than a range. You would have to transfer the values from the range to the internal table beforehand. This might be a little complex if the ranges contain more than just single values.

Thomas

Re: Maximum size of an SQL statement

Former Member — Fri, 12 Jun 2009 11:03:30 GMT

As this is the issue of the memory, you can contact your basis team for this.

Regards,

Vijay.

Re: Maximum size of an SQL statement

Former Member — Fri, 12 Jun 2009 11:06:07 GMT

Hi,

Have a look at this SAP note. Might be helpful

Note 635318 - Open SQL: Size restrictions for commands

Regards

Re: Maximum size of an SQL statement

Former Member — Fri, 12 Jun 2009 12:48:02 GMT

Hello,

Probably you get a short dump because your SQL statement exceeds the maximum number of 2090 rows(see error with transaction ST11 double click at dev_w0 ). Check the number of entries into selection option lv_sel_copy try to change your selection by using the option for all entries in table replacing the in lv_sel_copy

Re: Maximum size of an SQL statement

Former Member — Tue, 16 Jun 2009 07:32:41 GMT

Thanks for your help.

With the statement "for all entries in" it works fine.