`outputtypehandler` not respecting `NLS_TERRITORY` in thin mode

[CaselIT]

1. What versions are you using?

platform.platform: Windows-10-10.0.19044-SP0
sys.maxsize > 2**32: True
platform.python_version: 3.10.4
oracledb.__version__: 1.0.0

I'm using oracle 21.3.0.0, with client version (21, 3, 0, 0, 0)

2. Is it an error or a hang or a crash?

An unexpected result was returned

3. What error(s) or behavior you are seeing?

It seems that setting NLS_TERRITORY on the session and using outputtypehandler to return floating values to string does not take into consideration the set NLS_TERRITORY parameter

4. Does your application call init_oracle_client()?

both

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

import oracledb

thin_mode = True
if not thin_mode:
    oracledb.init_oracle_client()

def go():
    with oracledb.connect(
        user="scott",
        password="tiger",
        service_name="XEPDB1",
        host="localhost",
        port=11521,
    ) as conn:
        cursor = conn.cursor()
        # NLS_TERRITORY can be set to any nation that uses comma. also GERMANY works
        cursor.execute("ALTER SESSION SET NLS_TERRITORY='ITALY'")
        res = cursor.execute(
            "select cast(1.1 as varchar(128)) from dual"
        ).fetchall()[0]
        print("with cast", res[0], res[0] == "1,1")

        def output_type_handler(
            cursor, name, defaultType, size, precision, scale
        ):
            return cursor.var(oracledb.STRING, 255, arraysize=cursor.arraysize)

        cursor.outputtypehandler = output_type_handler
        res = cursor.execute("select 1.1 from dual").fetchall()[0]
        print("with outputtypehandler", res[0], res[0] == "1,1")

go()

with thin_mode = True the above script prints the following.

with cast 1,1 True
with outputtypehandler 1.1 False

the second print ignores the NLS_TERRITORY setting.
Setting thin_mode = False prints the expected

with cast 1,1 True
with outputtypehandler 1,1 True

[cjbj]

All NLS environment variables are ignored in Thin mode, see https://python-oracledb.readthedocs.io/en/latest/user_guide/globalization.html

All NLS environment variables are ignored by the python-oracledb Thin mode. Also the ORA_SDTZ and ORA_TZFILE variables are ignored. See Locale-aware Number and Date Conversions in python-oracledb Thin Mode.

[anthony-tuininga]

Take a look at this section in particular: https://python-oracledb.readthedocs.io/en/latest/user_guide/globalization.html#locale-aware-number-and-date-conversions-in-python-oracledb-thin-mode. That will work in both thick and thin modes.

[CaselIT]

Not sure why github posted this, I had the new issue open in another tab where I was adding a complete test and all before posting. Before doing that I decided to take another look at the doc I found that part.

Sorry for the noise.. closing

edit: since I had already written the test case I've updated the opening issue

[cjbj]

@CaselIT I'd love your comments on whether we need to replicate the Oracle globalization support in the Thin driver code to respect some/all NLS environment variables. Or do the Python solutions work (better)?

[CaselIT]

I can't really comment, since I don't really use oracle db for anything other that developing sqlalchemy, and I've otherwise never used globalization on the db side.

This issue was prompted by a test failure in the sqlalchemy test suite and poking at it I found the different behavior between the two modes.

What I can say is that a consistent behavior would be nice, but I understand that it may be a large amount of work for something that may not be very popular/requested (no clue here).

[cjbj]

@CaselIT thank you - every comment helps.