Python中用cx_Oracle调用Oracle函数遇ORA-06502错误及解决

问题场景 #

Oracle函数定义 #

function SetMessageInfo(  plogid                     number,
                          pmess_oper_status          number,
                          pmess_reason_code          number,
                          pmess_counter_measure_code number,
                          poper_trans_date           date) return number;

对应的Python调用代码 #

import datetime
import cx_Oracle

# 假设已完成数据库连接并获取游标curOracle
plogid = 215
pmess_oper_status = None
pmess_reason_code = 1
pmess_counter_measure_code = 1
now = datetime.datetime.now()

try:    
    result_end = curOracle.callfunc('PKG_IMPORT.setmessageinfo', int, [plogid, pmess_oper_status, pmess_reason_code, pmess_counter_measure_code, now.strftime('%Y-%m-%d %H:%M')])
except Exception as err:
    print('Can not execute setmessageinfo',err)
else:
    print('Succesfully executed setmessageinfo')

运行时错误 #

cx_Oracle.DatabaseError: ORA-06502: PL/SQL:数值或值错误:字符到数字转换错误

解决方法 #

1. 核心问题：使用cx_Oracle的callfunc方法调用PL/SQL函数时，必须传入所有参数，哪怕PL/SQL函数中部分参数设置了默认值（非必填）。遗漏参数会导致Oracle端参数匹配混乱，触发类型转换错误。
2. 日期参数优化：无需将datetime对象格式化为字符串传递，直接传入原生datetime对象即可，cx_Oracle会自动处理与Oracle DATE类型的转换，避免字符转日期的额外错误。

修正后的关键代码：

# 直接传入datetime对象，确保参数列表完整
result_end = curOracle.callfunc('PKG_IMPORT.setmessageinfo', int, [plogid, pmess_oper_status, pmess_reason_code, pmess_counter_measure_code, now])

内容的提问来源于stack exchange，提问作者O.Sartaev