Thin mode: PL/SQL BOOLEAN OUT bind decodes TRUE as False (always False) with DB_TYPE_BOOLEAN

[MalyshevDenSer]

1. What versions are you using?
   platform.platform: Windows-10-10.0.17763-SP0
   sys.maxsize > 2**32: True
   platform.python_version: 3.10.11
   oracledb.version: 3.4.1
   oracledb.is_thin_mode(): True
   oracle database version: 12.1.0.2.0
   platform.machine: AMD64
   platform.version: 10.0.17763
   platform.architecture: 64-bit

2. Is it an error or a hang or a crash?
   Behavior issue (incorrect result), no error/hang/crash.

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

import oracledb

print("oracledb:", oracledb.__version__, "thin:", oracledb.is_thin_mode())

conn = oracledb.connect(user="...", password="...", dsn="...")  # Thin mode
cur = conn.cursor()

b = cur.var(oracledb.DB_TYPE_BOOLEAN)
cur.execute("begin :x := TRUE; end;", x=b)
print("PL/SQL TRUE ->", b.getvalue())

b2 = cur.var(oracledb.DB_TYPE_BOOLEAN)
cur.execute("begin :x := FALSE; end;", x=b2)
print("PL/SQL FALSE ->", b2.getvalue())

# Control test / workaround: confirms server-side boolean values are correct
n = cur.var(int)
cur.execute("begin :x := sys.diutil.bool_to_int(TRUE); end;", x=n)
print("PL/SQL bool_to_int(TRUE) ->", n.getvalue())  # expected 1

n2 = cur.var(int)
cur.execute("begin :x := sys.diutil.bool_to_int(FALSE); end;", x=n2)
print("PL/SQL bool_to_int(FALSE) ->", n2.getvalue())  # expected 0

# Another boolean test:
b3 = cur.var(oracledb.DB_TYPE_BOOLEAN)
cur.execute("declare v boolean := TRUE; begin :x := NOT v; end;", x=b3)
print("PL/SQL NOT TRUE ->", b3.getvalue())  # expected False

cur.close()
conn.close()

Actual output:

PL/SQL TRUE -> False                 # !!!
PL/SQL FALSE -> False
PL/SQL bool_to_int(TRUE) -> 1
PL/SQL bool_to_int(FALSE) -> 0
PL/SQL NOT TRUE -> False

Expected output:

PL/SQL TRUE -> True                 # !!!
PL/SQL FALSE -> False 
PL/SQL bool_to_int(TRUE) -> 1
PL/SQL bool_to_int(FALSE) -> 0
PL/SQL NOT TRUE -> False

SYS.DIUTIL.BOOL_TO_INT(TRUE/FALSE) returning 1/0 indicates the server-side boolean evaluation is correct; the incorrect behavior appears specific to decoding PL/SQL BOOLEAN OUT bind values via DB_TYPE_BOOLEAN in Thin mode.

This is a silent incorrect result (no exception), which can cause application logic errors when PL/SQL functions/procedures return BOOLEAN values.

4. Does your application call init_oracle_client()?
   No. init_oracle_client() is not called, so the driver runs in Thin mode (oracledb.is_thin_mode() == True).

Note: I attempted to test in Thick mode by calling init_oracle_client() with an Oracle Client 11.2.0.4.0 installation, but python-oracledb fails to start Thick mode with:

oracledb.exceptions.DatabaseError: DPI-1050: Oracle Client library is at version 11.2 but version 12.1 or higher is needed

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

"""
Repro: PL/SQL BOOLEAN OUT bind in Thin mode returns incorrect value (TRUE decoded as False).

Environment variables required:
  ORA_USER, ORA_PASS, ORA_DSN

Example (Windows):
  set ORA_USER=apps
  set ORA_PASS=...
  set ORA_DSN=10.10.10.235:1551/WEEKLY
"""

import os
import platform
import sys
import oracledb


def main():
    print("platform.platform:", platform.platform())
    print("sys.maxsize > 2**32:", sys.maxsize > 2**32)
    print("platform.python_version:", platform.python_version())
    print("oracledb.__version__:", oracledb.__version__)
    print("oracledb.is_thin_mode():", oracledb.is_thin_mode())

    user = os.environ["ORA_USER"]
    pwd = os.environ["ORA_PASS"]
    dsn = os.environ["ORA_DSN"]

    conn = oracledb.connect(user=user, password=pwd, dsn=dsn)
    print("connection.version:", conn.version)

    cur = conn.cursor()

    b = cur.var(oracledb.DB_TYPE_BOOLEAN)
    cur.execute("begin :x := TRUE; end;", x=b)
    print("PL/SQL TRUE ->", b.getvalue())

    b2 = cur.var(oracledb.DB_TYPE_BOOLEAN)
    cur.execute("begin :x := FALSE; end;", x=b2)
    print("PL/SQL FALSE ->", b2.getvalue())

    # Control test / workaround: confirms server-side boolean values are correct
    n = cur.var(int)
    cur.execute("begin :x := sys.diutil.bool_to_int(TRUE); end;", x=n)
    print("PL/SQL bool_to_int(TRUE) ->", n.getvalue())  # expected 1

    n2 = cur.var(int)
    cur.execute("begin :x := sys.diutil.bool_to_int(FALSE); end;", x=n2)
    print("PL/SQL bool_to_int(FALSE) ->", n2.getvalue())  # expected 0

    # Another boolean test:
    b3 = cur.var(oracledb.DB_TYPE_BOOLEAN)
    cur.execute("declare v boolean := TRUE; begin :x := NOT v; end;", x=b3)
    print("PL/SQL NOT TRUE ->", b3.getvalue())  # expected False

    cur.close()
    conn.close()


if __name__ == "__main__":
    main()

[anthony-tuininga]

I'll take a look. 11.2 didn't support booleans -- you'll need to use a newer client to test thick mode! You should be using a newer client anyway. :-)

[anthony-tuininga]

This code works fine with database 12.2 and higher. Booleans weren't truly supported until version 12.2, so this is not unexpected! I suspect you'll find the same thing with thick mode using a newer client version, too. I have asked internally for such a database to verify the scenario and see if there is a way to work around the limitation.

[MalyshevDenSer]

I'll take a look. 11.2 didn't support booleans -- you'll need to use a newer client to test thick mode! You should be using a newer client anyway. :-)

You mean 11.2 Oracle Client?

[MalyshevDenSer]

This code works fine with database 12.2 and higher. Booleans weren't truly supported until version 12.2, so this is not unexpected! I suspect you'll find the same thing with thick mode using a newer client version, too. I have asked internally for such a database to verify the scenario and see if there is a way to work around the limitation.

But here you mean 12.2 Oracle Database?

[anthony-tuininga]

@MalyshevDenSer, yes to both questions. :-) Although now that you question it, it may be related to the client more than the database. All I know is there have been plenty of issues with boolean support prior to 12.2. Upgrading is very much recommended if you want to use booleans in any sane fashion!

[anthony-tuininga]

Ok, I got access to a 12.1 database. With thick mode using a recent client you get this error:

oracledb.exceptions.DatabaseError: ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 1

With thin mode the value returned by the database is "interesting" and explains the issue. I am asking internally about it and will get back to you.