NCHAR can't be bound for every string value in cx_Oracle

Issue #4242 open
Araam Borhanian
created an issue

When I run this date query, SQLAlchemy has cx_Oracle throw an error, but I can't replicate this with just cx_Oracle alone. This started showing up after version 1.2.2 of SQLAlchemy. Earlier versions don't have this issue. Occurs on all versions of cx_Oracle from 5.2.1 to 6.2.1

DB Version I'm running on is Oracle Database 11g Release 11.2.0.4.0 - 64bit Production

The stacktrace after running this code:

Traceback (most recent call last):
  File "...\.env\lib\site-packages\sqlalchemy\engine\base.py", line 1193, in _execute_context
    context)
  File "...\.env\lib\site-packages\sqlalchemy\engine\default.py", line 507, in do_execute
    cursor.execute(statement, parameters)
cx_Oracle.DatabaseError: ORA-01899: bad precision specifier

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "testcase.py", line 19, in <module>
    session.query(func.trunc(func.sysdate(), 'dd') - 1).all()
  File "...\.env\lib\site-packages\sqlalchemy\orm\query.py", line 2726, in all
    return list(self)
  File "...\.env\lib\site-packages\sqlalchemy\orm\query.py", line 2878, in __iter__
    return self._execute_and_instances(context)
  File "...\.env\lib\site-packages\sqlalchemy\orm\query.py", line 2901, in _execute_and_instances
    result = conn.execute(querycontext.statement, self._params)
  File "...\.env\lib\site-packages\sqlalchemy\engine\base.py", line 948, in execute
    return meth(self, multiparams, params)
  File "...\.env\lib\site-packages\sqlalchemy\sql\elements.py", line 269, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "...\.env\lib\site-packages\sqlalchemy\engine\base.py", line 1060, in _execute_clauseelement
    compiled_sql, distilled_params
  File "...\.env\lib\site-packages\sqlalchemy\engine\base.py", line 1200, in _execute_context
    context)
  File "...\.env\lib\site-packages\sqlalchemy\engine\base.py", line 1413, in _handle_dbapi_exception
    exc_info
  File "...\.env\lib\site-packages\sqlalchemy\util\compat.py", line 203, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "...\.env\lib\site-packages\sqlalchemy\util\compat.py", line 186, in reraise
    raise value.with_traceback(tb)
  File "...\.env\lib\site-packages\sqlalchemy\engine\base.py", line 1193, in _execute_context
    context)
  File "...\.env\lib\site-packages\sqlalchemy\engine\default.py", line 507, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.DatabaseError: (cx_Oracle.DatabaseError) ORA-01899: bad precision specifier [SQL: 'SELECT trunc(sysdate, :trunc_1) - :trunc_2 AS anon_1 FROM DUAL'] [parameters: {'trunc_1': 'dd', 'trunc_2': 1}] (Background on this error at: http://sqlalche.me/e/4xp6)

Comments (14)

  1. Michael Bayer repo owner

    the issue is #4163 and per cx_Oracle's author's advice your test case is essentially this:

    conn = cx_Oracle.connect(
        dsn=cx_Oracle.makedsn("oracle1120", 1521), user="scott", password="tiger")
    cursor = conn.cursor()
    cursor.setinputsizes(day=cx_Oracle.NCHAR)
    
    result = cursor.execute("SELECT trunc(sysdate, :day) - :back FROM DUAL",
                            {'day': 'dd', 'back': 1}).fetchall()
    cursor.close()
    conn.close()
    

    I've re-asked at https://github.com/oracle/python-cx_Oracle/issues/119#issuecomment-383157593 what we can do here. I'm basically working from cx_Oracle's author's advice.

  2. Michael Bayer repo owner

    here's how to fix Oracle's error here for now:

    from sqlalchemy import create_engine
    from sqlalchemy.orm import sessionmaker
    from sqlalchemy.sql.expression import func
    from sqlalchemy import literal, String
    
    
    some_engine = create_engine("oracle+cx_oracle://scott:tiger@oracle1120/", echo=True)
    Session = sessionmaker(bind=some_engine)
    session = Session()
    session.query(func.trunc(func.sysdate(), literal('dd', String)) - 1).all()
    

    I'm not really sure yet what can be done here because in python 3, "dd" is a unicode object. We tell the driver it's unicode based on the value.

  3. Michael Bayer repo owner

    not really sure I'm going to get you a better answer than that. "dd" is a Python unicode object, we have to bind it to NCHAR by default. we have no idea where it's going

  4. Michael Bayer repo owner

    Document how to opt-out of NCHAR for cx_Oracle

    Unfortunately, we need to bind Python unicode values as NCHAR as in the case where non-ascii characters are present, it's necessary. We can't know in all cases how this value is being used, so in those cases where Oracle will not accept NCHAR the user should explicitly cast a value down to String.

    Change-Id: I1a70739033435a7bf5effe2fa810ab064cea9188 Fixes: #4242

    → <<cset e6e1c02c96b0>>

  5. Michael Bayer repo owner

    Document how to opt-out of NCHAR for cx_Oracle

    Unfortunately, we need to bind Python unicode values as NCHAR as in the case where non-ascii characters are present, it's necessary. We can't know in all cases how this value is being used, so in those cases where Oracle will not accept NCHAR the user should explicitly cast a value down to String.

    Change-Id: I1a70739033435a7bf5effe2fa810ab064cea9188 Fixes: #4242 (cherry picked from commit e6e1c02c96b077700187420019194989ea55a646)

    → <<cset 013c076a9680>>

  6. Michael Bayer repo owner

    unfortunately I dont have a good idea on how to get SQLAlchemy to know when a Python unicode object should be framed as NCHAR and when it should not. This whole set of behaviors may change again but at the moment I don't have any good answers on this, no matter how I change it, it will break things for someone. in this case, the format specifier for "trunc" is a more special value than generalized unicode data being sent to columns so we are documenting the workaround in that direction.

    Perhaps if we mapped "unicode" to a new datatype that is like "PossibleUnicode" or something like that, this could give more of a clue to dialects that this was an ad-hoc string value which may be more appropriate to treat as non-unicode.

  7. Michael Bayer repo owner
    • changed status to open

    Anthony has a lot of advice at https://github.com/oracle/python-cx_Oracle/issues/119#issuecomment-383175345 and we should look into reverting #4163, looking at character set, writing new docs, all of that. this might be for 1.3 if it's really another big change:

    It took me a while to get my head wrapped around the issue myself so I'm not surprised that its confusing you, too! I understand that SQLAlchemy cannot know whether the database object which is being inserted or the procedure being called expects NCHAR data. The user does have to tell you, unfortunately. And you shouldn't use NCHAR exclusively as if the database object is CHAR and you bind as NCHAR then you have conversion going on and unnecessary overhead, just like if the database object is NCHAR and you bind as CHAR you have conversion and unnecessary overhead.

    NCHAR was developed many years ago to handle Unicode data back when the database character sets were single byte character sets. Since then multibyte character sets like AL32UTF8 can be used as the primary database character set, and in fact this is now the default! In that case, CHAR is perfectly capable of handling non-ASCII characters! And in fact this is what I use exclusively now. I avoid NCHAR like the plague that it is. :-) If your database primary character set is a single-byte character set, though, and you want to store strings that don't fit in that character set you have no choice but to use NCHAR, but this should be occurring less and less frequently as time progresses.

    It might make sense for you to check to see if the database character set is AL32UTF8 (the current default) and if so, ignore NCHAR completely by default. At that point, even if the user has set up NCHAR columns in tables anyway it won't fail. It will just be unnecessary conversion. And the user can solve that by simply migrating away from NCHAR columns completely.

    I hope that make sense now and I'm sorry if my earlier comments were confusing!

  8. Log in to comment