I want to call Oracle function within Python using cx_oracle
Oracle function:
function SetMessageInfo( plogid number,
pmess_oper_status number,
pmess_reason_code number,
pmess_counter_measure_code number,
poper_trans_date date) return number;
Python:
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')
and I get cx_Oracle.DatabaseError: ORA-06502: PL/SQL: numeric or value error: character to number conversion error.
UPD: Solved. When using cx_Oracle call_func need to pass as parameter all variables, i.e. even those which are not mandatory by PL/SQL function.
pmess_reason_codein the list of arguments, so it will be trying to implicitly convert your formatted date string to a number, which would throw that error. (But you will also have a problem passing that formatted string as a date argument, unless your Oracle session's NLS settings match. Pass the datetime, not a string.)