0

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.

3
  • You're missing pmess_reason_code in 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.) Commented Apr 17, 2023 at 11:20
  • @AlexPoole I'm sorry, it's my error while copying, actually pmess_reason_code is added as function parameter Commented Apr 17, 2023 at 11:27
  • 1
    If that is the case then what data types and values are those other arguments? Please edit your question to provide a minimal reproducible example. Commented Apr 17, 2023 at 11:33

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.