outconverter doesn't work when the value is Null

[lypch]

1. What versions are you using? platform.platform: Windows-10-10.0.19045-SP0 sys.maxsize > 2**32: True platform.python_version: 3.8.10

2. My database version cx_Oracle.version: 8.3.0 cx_Oracle.clientversion: (12, 1, 0, 2, 0)

3. Describe the problem

I have a requirement to get data from the database through cx_oracle convert. and during the fetch data, if the value of the Number field is None, it needs to be converted to -1.

I want to use outconverter attribute of the Variable. but I found if the value is None, the outconverter isn't fired.

4. Include a runnable Python script that shows the problem.

   
   import cx_Oracle

with cx_Oracle.connect("omrscpif" ,"omrscpif", 'ammiceng') as connect: def OutConverter(value): if value is None: return '' try: return value except: return ''

def NumberOutConverter(value):
    if value is None:
        return -1
    return value

def OutputTypeHandler(cursor, name, defaultType, size, precision, scale):
    if defaultType in (cx_Oracle.STRING, cx_Oracle.FIXED_CHAR):
        return cursor.var(str, size, cursor.arraysize, outconverter=OutConverter)

    if defaultType == cx_Oracle.DB_TYPE_NUMBER:
        return cursor.var(cx_Oracle.DB_TYPE_NUMBER, size, cursor.arraysize, outconverter=NumberOutConverter)

# Finally, we will assign the defination to the outputtypehandler of the connect.
connect.outputtypehandler = OutputTypeHandler

with connect.cursor() as cursor:
    cursor.execute("select fordertypevalue, forderqtyfuturecommitted, fbackorderqty from so_send where ftrnflg = 'N'")
    result = cursor.fetchall()
    print(result)

5. the result is below

[(None, None, 1.0)]

[cjbj]

Whenever reporting an issue, please share the CREATE TABLE statement and an INSERT statement with data. Thanks! This can save us time, and sometimes it is critical to being able to reproduce a problem.

[lypch]

@cjbj Noted, this is my first time to post a question. below is the sql statement to create the table and insert one row.

create table so_send(
  fordertypevalue char(2),
  forderqtyfuturecommitted number,
  fbackorderqty number
);

insert into so_send(fordertypevalue, forderqtyfuturecommitted, fbackorderqty )
values(null, null, 1);
commit;

[anthony-tuininga]

I already replied to the StackOverflow question you posted. As noted in the documentation this is not supported currently. If you would like this support, you can create an enhancement request for the python-oracledb driver.

[lypch]

@anthony-tuininga Thanks for your reply, it's very useful in practice, please enhance it in the new version. I have created an enhancement request, please refer to the link https://github.com/oracle/python-oracledb/issues/107#issue-1463937378

[cjbj]

Closing this since the discussion has moved to the python-oracledb repo.