CODEWITHSUNDEEP
pythonoracle-databasecx-oracle
Avangard
Avangard

Reputation: 31

ORA-00939: too many arguments for function, Im using cx_oracle in pycharm to pull from database, and encountering this error below:

the length of the variable hour is 792 and the uitid is 15, so both under 1000, so I don't know why I recieve this error, I tried it two ways as it can be seen below:

first way:


def generate_sql_in_binds(
    name: str,
    size: int,
    start: int = 1,
    max_binds: int = 1000,
) -> str:
    in_clauses = (
        "{name} IN ({binds})".format(
            name=name,
            binds=",".join(
                (
                    f":{b+start}"
                    for b in range(i, min(i+max_binds,size))
                )
            )
        )
        for i in range(0, size, max_binds)
    )
    return "(" + (" OR ".join(in_clauses)) + ")"


and the error is 
[![enter image description here][1]][1]

Upvotes: 0

Views: 116

Answers (1)

MT0
MT0

Reputation: 168232

You need to put TO_DATE in the iterator that generates the bind parameters so that it wraps each bind parameter (rather than trying to wrap the entire list):

query = """SELECT HOUR, UNITSCHEDULEID, VERSIONID, MINRUNTIME
FROM  int_Stg.UnitScheduleOfferHourly
WHERE Hour in ({hour_binds})
AND   UnitScheduleId in ({id_binds})""".format(
  hour_binds=",".join(
    (f"TO_DATE(:{idx}, 'dd-mon-yyyy hh24')" for idx in range(1, len(hour) + 1))
  ),
  id_binds=",".join(
    (f":{idx}" for idx in range(len(hour) + 1, len(unitid) + len(hour) + 1))
  ),
)
res = connection.cursor().execute(query, (*hour, *unitid)).fetchall()
print(res)

However, you would probably be better to leave the query as it is and convert the list of strings to a list of datetimes.

from datetime import datetime

hour_dt = tuple(datetime.strptime(value, "%d-%b-%Y %H") for value in hour)

query = """SELECT HOUR, UNITSCHEDULEID, VERSIONID, MINRUNTIME
FROM  int_Stg.UnitScheduleOfferHourly
WHERE Hour in ({hour_binds})
AND   UnitScheduleId in ({id_binds})""".format(
  hour_binds=",".join(
    (f":{idx}" for idx in range(1, len(hour_dt) + 1)),
  ),
  id_binds=",".join(
    (f":{idx}" for idx in range(len(hour_dt) + 1, len(unitid) + len(hour) + 1)),
  ),
)

res = connection.cursor().execute(query, (*hour_dt, *unitid)).fetchall()
print(res)

Upvotes: 1

Related Questions