User should not have to specify cur.setinputsizes(l_sysdate=oracledb.Date) in this case

[mkmoisen]

1. What versions are you using?

   >>> import oracledb
   >>> oracledb.__version__
   '1.3.2'

   Give your database version.

   Oracle 19c

Also run Python and show the output of:

>>> import sys
>>> import platform
>>>
>>> print("platform.platform:", platform.platform())
platform.platform: Linux-5.15.90.1-microsoft-standard-WSL2-x86_64-with-glibc2.28
>>> print("sys.maxsize > 2**32:", sys.maxsize > 2**32)
sys.maxsize > 2**32: True
>>> print("platform.python_version:", platform.python_version())
platform.python_version: 3.11.4

And:

>>> import oracledb
>>> oracledb.__version__
'1.3.2'

-->

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

Error

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

Using a null bind variable in this statementAND NVL(:l_sysdate, SYSDATE) >= foo.c1 doesn't appear to work by default.

It seems the only way to make it work is by calling cur.setinputsizes(l_sysdate=oracledb.Date) in advance.

I tried doing this from both sqlplus and sqldeveloper, where it seems to work fine without having to specify any date data type.

Database setup:

create table foo (c1 date);
insert into foo values (sysdate - 1/24);
commit;
select * from foo;

select sysdate, c1 from foo;
2023-09-07 08:43:44 2023-09-07 07:38:16

Python:

cur = conn.cursor()

l_sysdate = None

# Here we try using NVL(:l_sysdate, SYSDATE) which returns nothing

result = cur.execute(
    '''
    SELECT COUNT(1)
    FROM foo
    WHERE 1=1
        AND NVL(:l_sysdate, SYSDATE) >= foo.c1
    ''',
    dict(
        l_sysdate=None
    )
).fetchone()

# This should be 1, but it is 0
assert result[0] == 0

# But NVL(NULL, SYSDATE) works as expected

result = cur.execute(
    '''
    SELECT COUNT(1)
    FROM foo
    WHERE 1=1
        AND NVL(NULL, SYSDATE) >= foo.c1
    ''',
).fetchone()

# This is correctly 1
assert result[0] == 1

## Work around

# Calling this seems to fix it
cur.setinputsizes(l_sysdate=oracledb.Date)

result = cur.execute(
    '''
    SELECT COUNT(1)
    FROM foo
    WHERE 1=1
        AND NVL(:l_sysdate, SYSDATE) >= foo.c1
    ''',
    dict(
        l_sysdate=None
    )
).fetchone()

# It works now
assert result[0] == 1

It works fine when I use sqlplus or sqldeveloper. I don't need to specify any date datatype for it to work.

Use a gist for long screen output and logs: see https://gist.github.com/

-->

5. Does your application call init_oracle_client()?

Yes

This tells us whether you are using the python-oracledb Thin or Thick mode.

Thick

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

See above

[anthony-tuininga]

Can you show me what you did in SQL*Plus that worked?

[mkmoisen]

@anthony-tuininga

Sorry, it only works in SQLDeveloper, not in SQLPlus.

I ran this in SQLPlus

var l_sysdate varchar2(128);

-- This does not work in sqlplus:

SELECT COUNT(1)
FROM foo
WHERE 1=1
AND NVL(:l_sysdate, SYSDATE) > c1;

-- This however with TO_DATE does work in sqlplus:

SELECT COUNT(1)
FROM foo
WHERE 1=1
AND NVL(TO_DATE(:l_sysdate, 'YYYY-MM-DD HH24:MI:SS'), SYSDATE) > c1;

---

In sqldevloper, this seems to work:

select count(1)
from foo
where 1=1
    and nvl(:l_sysdate, sysdate) > c1
;

I'm not sure what sqldeveloper is doing to make this work though.

[anthony-tuininga]

Thanks for the clarification. This is expected behavior. The value None in Python which is translated to null in Oracle does not have a "type". So the driver is forced to "guess" -- and the driver always uses VARCHAR2(1) -- so your "workaround" is the correct way of telling the database what type you are actually using. I'm not sure what SQLDeveloper is doing. It may be analyzing the statement and adjusting accordingly.

[mkmoisen]

@anthony-tuininga

Thanks. Do you happen to know if there is any more intuitive way to accomplish this, other than cur.setinputsizes(l_sysdate=oracledb.Date)?

I'm actually using SQLAlchemy on top of cx_Oracle and could not get it to work at all.

I tried variations on TO_DATE(:l_sysdate) but also couldn't get it working.

[anthony-tuininga]

This works for me:

result = cur.execute(
    '''
    SELECT COUNT(1)
    FROM issue_227
    WHERE 1=1
        and nvl(to_date(:l_sysdate), sysdate) >= issue_227.c1
    ''',
    dict(
        l_sysdate=None
    )
).fetchone()

result = cur.execute(
    '''
    SELECT COUNT(1)
    FROM issue_227
    WHERE 1=1
        and nvl(to_date(:l_sysdate), sysdate) >= issue_227.c1
    ''',
    dict(
        l_sysdate=datetime.datetime(2023, 10, 1)
    )
).fetchone()