outconverter doesn't work when the value is null

[lypch]

1. Describe your new request in detail
   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.
   it's very useful in practice, please enhance it in the new version. thanks a lot.

2. Give supporting information about tools and operating systems. Give relevant product version numbers
   platform.platform: Windows-10-10.0.19045-SP0
   sys.maxsize > 2**32: True
   platform.python_version: 3.8.10
   cx_Oracle.version: 8.3.0
   cx_Oracle.clientversion: (12, 1, 0, 2, 0)

3. I have posed the same question in the following link.
   outconverter doesn't work when the value is Null python-cx_Oracle#640 (comment)

[lypch]

@anthony-tuininga I want to know when you will finish it. because my project needs this enhancement.

[anthony-tuininga]

Since all existing outconverters were run only when the input value was not None, changing the behavior to to run for all values may break existing code. As such, I think this change will need to be phased in -- probably by use of the __future__ object -- and that means it may take some time. Are you planning on using thick mode or thin mode? I can share the final changes and you can make them yourself temporarily until a release is out that supports this.

[lypch]

@anthony-tuininga Thanks for your reply and suggestion. Currently, I prefer the thin mode. it's my horner to get help from you. please tell me how to do it.
by the way, the thin mode means the no need to install the oracle client?

[lypch]

@anthony-tuininga Sorry, I check the document. the thin mode just supports version started 12.1. but our customers might still use the lower version. so if can, please tell me the both thick mode and thin mode. thanks a lot

[anthony-tuininga]

For thick mode, the answer is easy enough. You just have to unindent these three lines one level.

[lypch]

Great. Thank you. @anthony-tuininga

[anthony-tuininga]

I have pushed a patch that implements this. If you are able to build from source you can verify that it corrects your issue as well.

One example (that also makes use the of new enhanced metadata, another enhancement for 1.4):

import oracledb

def type_handler(cursor, metadata):
    if metadata.type_code is oracledb.DB_TYPE_VARCHAR:
        return cursor.var(str, outconverter=lambda v: f'|{v}|' if v else '',
                          convert_nulls=True, arraysize=cursor.arraysize)

conn = oracledb.connect("user/password@host/service_name")
conn.outputtypehandler = type_handler
cursor = conn.cursor()
cursor.execute("""
        select 'First row part A', 'First row part B' from dual
        union all
        select 'Second row part A', null from dual
        union all
        select null, 'Third row part B' from dual""")
for row in cursor:
    print(row)

This produces the output:

('|First row part A|', '|First row part B|')
('|Second row part A|', '')
('', '|Third row part B|')

[anthony-tuininga]

This has been included in python-oracledb 1.4.0 which was just released.

[lypch]

Sorry, I am very busy these days and haven't tested it, but I think it is no problem. thanks a lot.