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!