解决Python连接Oracle数据库的ORA-01790数据类型错误

原因分析 #

ORA-01790错误的核心是UNION ALL要求对应位置的列必须具有相同的数据类型。当传入None（对应Oracle的NULL）时，Oracle无法自动推断该参数的类型，导致与UNION另一侧的同列类型不匹配，从而触发错误。

解决方案 #

方案1：在SQL中显式转换NULL参数的类型 #

通过CAST函数为含NULL的参数指定明确的数据类型，确保UNION两边列类型一致。修改后的SQL如下：

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

注：请根据实际字段类型替换NUMBER，比如VARCHAR2(50)、DATE等。

方案2：使用cx_Oracle的NULL常量绑定参数 #

直接用cx_Oracle.NULL替代Python的None，让Oracle明确识别参数类型：

import cx_Oracle

data = [1, 2, cx_Oracle.NULL, 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()

方案3：显式设置参数输入类型 #

通过cursor.setinputsizes()方法为每个绑定参数指定数据类型，即使参数为None，Oracle也能明确其类型：

import cx_Oracle

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()
# 根据实际字段类型设置，示例为所有字段都是NUMBER类型
cur.setinputsizes(
    cx_Oracle.NUMBER,
    cx_Oracle.NUMBER,
    cx_Oracle.NUMBER,
    cx_Oracle.NUMBER,
    cx_Oracle.NUMBER,
    cx_Oracle.NUMBER
)
cur.execute(sql, data)
data = cur.fetchall()

内容的提问来源于stack exchange，提问作者Finrod Felagund