Closed felixxm closed 6 months ago
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
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.
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.
The patch has been included in version 2.0.0 which was just released.
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.
Oracle 23c
Error
The following query raises
ORA-00920: invalid relational operator
when
:arg0
is a parameter withinput_size = Database.DB_TYPE_BOOLEAN
, it seems that is converted to theNUMBER
.No.
I can debug my issue and provide details about why and where
oracledb
is at fault, but first I wanted to confirm thatDB_TYPE_BOOLEAN
can be used within SQL and it's not something that you work on. Thanks.