Help regarding SQL Alchemy Oracle Error

[venu v]

Hi,

I have sql alchemy , cx_Oracle installed , Python 2.7 installed on my windows machine.

Trying to run a sql using SQL Alchemy and it fails with the below error:

"sqlalchemy.exc.DatabaseError: (cx_Oracle.DatabaseError) ORA-00906: missing left parenthesis"

This is the SQL i have:

('SELECT count(:count_2) AS count_1 \nFROM measure \nWHERE lower(CAST(measure.measure_code AS NVARCHAR2)) LIKE lower(:param_1) OR lower(CAST(measure.measure_name AS NVARCHAR2)) LIKE lower(:param_2)' , {'param_1': u'%da%', 'count_2': '*', 'param_2': u'%da%'})

Stack Trace:

Traceback (most recent call last):

  File "<stdin>", line 1, in <module>

  File "C:\Project\flask-admin-project\lib\site-packages\sqlalchemy\engine\base.py", line 939, in execute

    return self._execute_text(object, multiparams, params)

  File "C:\Project\flask-admin-project\lib\site-packages\sqlalchemy\engine\base.py", line 1097, in _execute_text

    statement, parameters

  File "C:\Project\flask-admin-project\lib\site-packages\sqlalchemy\engine\base.py", line 1189, in _execute_context

    context)

  File "C:\Project\flask-admin-project\lib\site-packages\sqlalchemy\engine\base.py", line 1402, in _handle_dbapi_exception

    exc_info

  File "C:\Project\flask-admin-project\lib\site-packages\sqlalchemy\util\compat.py", line 203, in raise_from_cause

    reraise(type(exception), exception, tb=exc_tb, cause=cause)

  File "C:\Project\flask-admin-project\lib\site-packages\sqlalchemy\engine\base.py", line 1182, in _execute_context

    context)

  File "C:\Project\flask-admin-project\lib\site-packages\sqlalchemy\engine\default.py", line 470, in do_execute

    cursor.execute(statement, parameters)

sqlalchemy.exc.DatabaseError: (cx_Oracle.DatabaseError) ORA-00906: missing left parenthesis [SQL: 'SELECT count(:count_2) AS count_1 \nFROM measure \nWHERE lower(CAST(measure.measure_code AS N

ARCHAR2)) LIKE lower(:param_1) OR lower(CAST(measure.measure_name AS NVARCHAR2)) LIKE lower(:param_2)'] [parameters: {'param_1': u'%da%', 'count_2': '*', 'param_2': u'%da%'}]

I modified the SQL to remove the CAST function and tried again . It worked.

As the first SQL is system generated SQL, i cannot modify it .

Is there any way i can force SQL Alchemy not to do CAST on the measure code column?

Request you please help.

Thanks,

Venugopal.

[Mike Bayer]

those NVARCHAR2's probably need a length:

CAST(expr AS NVARCHAR2(20))

> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy+...@googlegroups.com.
> To post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[venu v]

Thanks Mike. The sql is being created at runtime and i am not even creating the sql.

Any inputs on how we can change it?.

Appreciate your response.

Thanks.

[Mike Bayer]

On Mon, Nov 13, 2017 at 2:50 PM, venu v <venugo...@gmail.com> wrote:
> Thanks Mike. The sql is being created at runtime and i am not even creating
> the sql.
> Any inputs on how we can change it?.

if you are using the String(), Unicode(), or NVARCHAR2() constructs,
give it a length:

cast(expr, Unicode(50))