Fixing datatype error in oracledb from Python ORA-01790

Question

I have the following code:

data = [1,2,None,4,5,6]
sql = """
SELECT :1 AS ID, :2 AS pkey, :3 AS issuenum FROM dual  UNION ALL
SELECT :4 AS ID, :5 AS pkey, :6 AS issuenum FROM dual 
"""

cur = con.cursor()
cur.execute(sql, data)
data = cur.fetchall()

I gen an error DatabaseError: ORA-01790: expression must have same datatype as corresponding expression

If i run it with data = [1,2,3,4,5,6], it all passes.

From the error it seems, it doesn't like the missing value. My question is, how do I bid the statement so that I can select NULL value, where the None is in the list?

Answer 1

This is a SQL issue and nothing specific to the driver. You can fix this issue by issuing the following statement instead:

SELECT :1 AS ID, :2 AS pkey, to_number(:3) AS issuenum FROM dual
UNION ALL
SELECT :4 AS ID, :5 AS pkey, to_number(:6) AS issuenum FROM dual

This will ensure that the data type is number for both rows. In Python the value None has no type so the driver assumes the value is a string.

You can also work around this issue in the driver by issusing the following command prior to executing the SQL:

cur.setinputsizes(
    oracledb.DB_TYPE_NUMBER,
    oracledb.DB_TYPE_NUMBER,
    oracledb.DB_TYPE_NUMBER,
    oracledb.DB_TYPE_NUMBER,
    oracledb.DB_TYPE_NUMBER,
    oracledb.DB_TYPE_NUMBER,
)

This forces the driver to treat all of the bind values as number, including the value None.