sqlalchemy-datatables
sqlalchemy-datatables copied to clipboard
Error during search with oracle: ORA-00932: inconsistent datatypes: expected - got CLOB
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.
I expect you can fix this by casting to String, not Text. Postgres doesn't care about the difference, Orcale apparently does