CODEWITHSUNDEEP
sqloracle-databasepython-3.6cx-oracle
RK.
RK.

Reputation: 617

ORA-01795: maximum number of expressions in a list is 1000 from python and cx_oracle

I'm passing empList which is having 500k records as an input to my query, but my python cx_oracle code failing with below error. I need result for all those 500k records. How to get this without creating a temp table? It would be good if i pass it as a list or dataframe.

Error :

cx_Oracle.DatabaseError: ORA-01795: maximum number of expressions in a list is 1000

curs.execute("select ID,NAME,SAL from EMP where ID in "+str(tuple(empList))+"  ")

Solution :: I've resolved the issue by splitting and sending 1000 records each time like below. And performance wise also good.

repeat = 1
for i in range(0, len(myList), 1000):
    for j in range(i, 1000*repeat):
        if j+1 <= len(myList):
            tempList.append(myList[j])
    //Further processing logic
    tempList = []
    repeat += 1

Upvotes: 0

Views: 812

Answers (1)

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28413

Oracle does allow > 1000 Tuples but not simple values. More on this here and here,

You could make use of Tuples

SELECT ID, NAME, SAL
FROM EMP
WHERE (1, ID) IN ((1, value1), (1, value2), (1, value3),.....(1, value5000));

This is of course if you don't have the option of using a subquery inside IN to get the values you need from a temp table.

SELECT ID, NAME, SAL FROM EMP where ID IN (SELECT values from <temporary-table>);

Upvotes: 0

Related Questions