Using DB_TYPE_BOOLEAN in SQL on Oracle 23c.

[felixxm]

Docs state that DB_TYPE_BOOLEAN can be used only within PL/SQL, however it should be possible to use in SQL since Oracle 23c.

1. What versions are you using?

Oracle 23c

platform.platform: Linux-5.15.0-89-generic-x86_64-with-glibc2.35
sys.maxsize > 2**32: True
platform.python_version: 3.10.9
oracledb.__version__: 1.4.2

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

Error

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

The following query raises ORA-00920: invalid relational operator

SELECT NOT :arg0 FROM "SOME_TABLE"

when :arg0 is a parameter with input_size = Database.DB_TYPE_BOOLEAN, it seems that is converted to the NUMBER.

4. Does your application call init_oracle_client()?

No.

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

I can debug my issue and provide details about why and where oracledb is at fault, but first I wanted to confirm that DB_TYPE_BOOLEAN can be used within SQL and it's not something that you work on. Thanks.

[cjbj]

I could have sworn we'd updated doc.

This is what I get with 1.4.2 & 23.4:

with connection.cursor() as cursor:
    bv = cursor.var(oracledb.DB_TYPE_BOOLEAN)
    bv.setvalue(0, True)
    for r, in cursor.execute('select not :bv from dual', [bv]):
        print(r)       # False
        b = bv.getvalue()
        print(b)       # True

with connection.cursor() as cursor:
    bv = cursor.var(oracledb.DB_TYPE_BOOLEAN)
    bv.setvalue(0, 1)
    for r, in cursor.execute('select not :bv from dual', [bv]):
        print(r)       # False
        b = bv.getvalue()
        print(b)       # True

with connection.cursor() as cursor:
    bv = cursor.var(oracledb.DB_TYPE_BOOLEAN)
    bv.setvalue(0, False)
    for r, in cursor.execute('select not :bv from dual', [bv]):
        print(r)       # True
        b = bv.getvalue()
        print(b)       # False

with connection.cursor() as cursor:
    bv = cursor.var(oracledb.DB_TYPE_BOOLEAN)
    bv.setvalue(0, 0)
    for r, in cursor.execute('select not :bv from dual', [bv]):
        print(r)       # True
        b = bv.getvalue()
        print(b)       # False

[anthony-tuininga]

With this script:

import oracledb

conn = oracledb.connect("user/password@host/service_name")
cursor = conn.cursor()

cursor.execute("select not :arg0 from dual", [True])
print("result (1):", cursor.fetchall())
cursor.execute("select not :arg0 from dual", [False])
print("result (2):", cursor.fetchall())

I get the error ORA-00920: invalid relational operator.

If I add this call:

cursor.setinputsizes(oracledb.DB_TYPE_BOOLEAN)

just before the first execute, it succeeds. So it would seem likely that the boolean value is being converted to an integer by default -- which it should not be doing when talking to a 23c database. I'll correct that! Based on this result it would also seem that your call to setinputsizes() is not taking effect.

[anthony-tuininga]

I have pushed a patch that should correct this bug. If you are able to build from source you can verify that it works for you.

[anthony-tuininga]

The patch has been included in version 2.0.0 which was just released.