Error during search with oracle: ORA-00932: inconsistent datatypes: expected - got CLOB

[nkgilley]

I am getting the following error when I try to perform a global search while using an oracle database. I'm not sure if this is an issue with sqlalchemy-datatables, sqlalchemy, or cx_Oracle.

DataTables warning: table id=userTable - (cx_Oracle.DatabaseError) ORA-00932: inconsistent datatypes: expected - got CLOB
[SQL: SELECT count(*) AS count_1 
FROM (SELECT users.fname AS users_fname 
FROM users 
WHERE lower(CAST(users.fname AS CLOB)) LIKE lower(:param_1)) anon_1]
[parameters: {'param_1': '%a%'}]

This query fails with the same error in sqlplus. I can fix it there manually by changing lower(CAST(users.fname AS CLOB)) LIKE lower(:param_1) to lower(to_char(users.fname)) LIKE lower(:param_1)

I was able to get rid of the error by removing the .cast(Text) from the filter_for function in datatables.py. This change will break searches for a postgres database.

[tdamsma]

I expect you can fix this by casting to String, not Text. Postgres doesn't care about the difference, Orcale apparently does