oracledb.callfunc returns error: Database error calling test_function: DPY-2007: expected a type

[tmpjr]

I created a simple oracle function to demonstrate this issue:

CREATE OR REPLACE FUNCTION test_function ( my_in_var IN VARCHAR2, my_out_param OUT VARCHAR2 ) RETURN NUMBER IS BEGIN dbms_output.put_line('my_in_var: ' || my_in_var); my_out_param := 'this is a test, it worked!'; RETURN 999; END;

Calling it within Oracle works fine:

DECLARE my_in_var VARCHAR2(100) := 'test in var'; my_out_param VARCHAR2(100); my_out_var NUMBER; BEGIN my_out_var := test_function(my_in_var, my_out_param); dbms_output.put_line('my_out_param: ' || my_out_param); dbms_output.put_line('my_out_var: ' || my_out_var); end;

Calling it with python-oracledb 2.2.1 and python 3.12 returns an error:
Database error calling test_function: DPY-2007: expected a type

with db.cursor() as cursor: my_in_var = 'test IN var...' my_out_param = cursor.var(oracledb.DB_TYPE_VARCHAR) my_ret_val = cursor.var(oracledb.DB_TYPE_NUMBER) params = { "my_in_var": my_in_var, "my_out_param": my_out_param } try: cursor.callfunc( "test_function", my_ret_val, keywordParameters=params ) message = my_out_param.getvalue() logger.info( f"Out Message: {message}" ) return True except oracledb.DatabaseError as exception: (error,) = exception.args message = error.message logger.error(f"Database error calling test_function: {message}") return False

I've tried cursor.var(int) and various other iterations but nothing seems to work. Is this a bug or am I doing something wrong? This all worked with cx_Oracle without any issues.

If it matters, it's being run in an AWS Lambda using the following layer/Dockerfile. It's Oracle 19.

FROM public.ecr.aws/lambda/python:3.12 RUN dnf upgrade --refresh RUN dnf install -y wget zip libaio libnsl RUN mkdir -p /layer/python /zip WORKDIR /layer RUN pip install oracledb -t python/ RUN wget https://download.oracle.com/otn_software/linux/instantclient/1923000/oracle-instantclient19.23-basiclite-19.23.0.0.0-1.x86_64.rpm RUN rpm -i oracle-instantclient19.23-basiclite-19.23.0.0.0-1.x86_64.rpm RUN rm -f oracle-instantclient19.23-basiclite-19.23.0.0.0-1.x86_64.rpm RUN cp -r /usr/lib/oracle/19.23/client64/lib/ /layer/ RUN cp /usr/lib64/libnsl.so.1 lib/ RUN cp /usr/lib64/libaio.so.1 lib/ RUN zip -r -y /zip/oracledb-layer.zip python/ lib/ 

[anthony-tuininga]

It is actually simpler than you think! :-)

with db.cursor() as cursor: my_in_var = 'test IN var...' my_out_param = cursor.var(oracledb.DB_TYPE_VARCHAR) params = { "my_in_var": my_in_var, "my_out_param": my_out_param } try: my_ret_val = cursor.callfunc( "test_function", oracledb.DB_TYPE_NUMBER, keyword_parameters=params ) message = my_out_param.getvalue() logger.info( f"Out Message: {message}" ) return True except oracledb.DatabaseError as exception: (error,) = exception.args message = error.message logger.error(f"Database error calling test_function: {message}") return False

Note the documentation which also contains a reference to a sample that should help further.

[tmpjr]

Thank you so much. That did the trick. If I could ask a follow up question. I am having issues with the out param of a large legacy function. What would cause the following error?

"oracledb.Var of type DB_TYPE_VARCHAR with value None"

My procedure has an out VARCHAR2 with that value. It is a very large proc that I can't share here due to IP. I was wondering if you could just tell me generally what causes that error. Thanks for the help!

[anthony-tuininga]

You're welcome!

"oracledb.Var of type DB_TYPE_VARCHAR with value None"

That doesn't look like an error message. It looks like the repr() of a variable! Can you provide the traceback and the actual error? I can try to comment based on that information. Note that I don't need the contents of the PL/SQL procedure at all. All that is needed is the PL/SQL procedure or function header and the way you are calling it in Python.

[tmpjr]

FUNCTION update_plate ( barcode IN VARCHAR2, parentbc IN VARCHAR2, row IN NUMBER, col IN NUMBER, op_status OUT VARCHAR2 ) RETURN NUMBER;

def update_plate(barcode, parent_bc, row, col): with db.cursor() as cursor: op_status = cursor.var(oracledb.DB_TYPE_VARCHAR) op_return = oracledb.DB_TYPE_NUMBER params = { "barcode": barcode, "parentbc": parent_bc, "row": row, "col": col, "op_status": op_status } try: cursor.callfunc("update_plate", op_return, keywordParameters=params) message = op_status.getvalue() logger.info( f"Updating plate info for barcode {barcode}. Message: {op_status}" ) return True except oracledb.DatabaseError as exception: (error,) = exception.args message = error.message logger.error(f"Database error calling {func_name}: {message}") return False

The Cloudwatch logs bring back the following message. Line 325 is the logger.info line above where it is printing the output from the proc. It's definitely having an issue subbing that value into the f-string.

{"level":"INFO","location":"update_plate:325","message":"Updating plate info for barcode XXXXXX. Message: <oracledb.Var of type DB_TYPE_VARCHAR with value None>","timestamp":"2024-06-17 21:38:54,193+0000","service":"my-listener","xray_trace_id":"1-6670aceb-747755b66a9db5b85c6e7fb7"} 

My suspicion is that my oracle procedure might be failing but not throwing an exception. I don't expect you to troubleshoot that but rather just point me in a direction if at all possible.

[anthony-tuininga]

You have a minor bug in your code. You are printing out the value of op_status which is a variable but you really want message which is the result of op_status.getvalue()! Everything else looks just fine!

[tmpjr]

Unbelievable. Can't believe I did not notice that. Thanks for going above and beyond. Now I can focus on this 2000 line PLSQL procedure that is a nightmare to troubleshoot.

[tmpjr]

Thanks!