How to solve error ORA-01795 : maximum number of expressions in a list is 1000 when running query

Learning Hub Academic Alliance
1

Hello guys

I am trying to execute a query and it gives this error… i generated this list from a data column and its over 100,000 if i choose to pick them in chunks of 1000 means the query would have to run 100 times and this would slow down my process.
Any work around please

regards

2

@supermanPunch :sweat_smile:

3

@MasterOfLogic What is the Query you used ?

4

“Select msg_num,session_num,response_code,reversal_num,payment_num From xxx.xxx_xxxxx_xxxxx@xxx_xxxxx Where message_id in ('” + sessionStrings + “')”

5

@MasterOfLogic Is sessionStrings a List variable and does it contain more than 1000 items ?

6

yes its just a string with every item in it placed in a quote and separated by a comma and its actually more that 1000, its above 100, 000

7

I used And milliDTA.AsEnumerable().[Select](Function(x) x(0).ToString()).Aggregate(Function(a, b) String.Concat(a, “‘,’” & b)) to generate the string that way

8

@MasterOfLogic Will there be repeated values in that list, if so, You can Try to use Distinct method and Check how much the Count gets reduced to.

9

@supermanPunch they are all distinct already…

10

they are really above 100,000 for today and are all distinct … this is the first time i am encountering this and i didn’t build the process to handle such an instance … hence its making the code to run in almost infinite loop

11

@supermanPunch The thing is that normally the logic there was like our previous logic… it takes 1000 rows of the data table converts it to a list of string and runs the query , then goes for the next until its done with the entire row.
But this time the rows are 100,000 and its going to have to do this 100 times…
which is so slow.
now i decided to change the row chunk to 10,000 and i got that error after changing it.
i discovered maximum strings in a list can only be 1000 strings …

12

@MasterOfLogic That error is a known issue according to this Topic :

Maybe You can read the Complete Table from the Database as a Datatable and then apply the Filter Condition using that String using a Linq. Have you tried it ?

I may have to check the previous post to understand it completely again :sweat_smile:

13

@supermanPunch heyy the link its not opening here

14

@MasterOfLogic I have updated the link just now.

15

Okay thanks … its says remove some expression from the list, but i really want to run all references at the same time

16

How a suggestion i saw online was like this

“Select msg_num,session_num,response_code,reversal_num,payment_num From xxx.xxx_xxxxx_xxxxx@xxx_xxxxx Where message_id in (’” + sessionStrings + “’) or message_id in (’” + sessionStrings + “’) ”… but i really don’t know what logic would split session strings into chunks again … i try to think of one it confuses me…lol

17

@MasterOfLogic I think it’s not possible using that String in a Sql query :sweat_smile: But have you tried the way I have mentioned above.

If this cannot be done then you would have to take in Chunks :sweat_smile: otherwise we would need to figure out an External Logic to it

1 Like
18

This we can do it but again it is around 100000 so there would be multiple Or’s. But this data is also not fixed so we can’t make it dynamic and hence I don’t think we can use it :sweat_smile:

1 Like
19

Exactly :joy: :sob:

1 Like
20

@MasterOfLogic Have you tried with lesser amount of items in the String Like 100 or 200 ?

1 Like