Generated comparison for Oracle CLOB column is incorrect

Thread is closed for posting
2 posts, 0 answers
  1. Constantine
    Constantine avatar
    7 posts
    Member since:
    May 2014

    Posted 14 Oct 2014 Link to this post


    1. Use Oracle database.
    2. Create table with CLOB column.
    3. Write LINQ query to compare content of this column with string. For example, 'database.Users.Any(u => u.Email.ToUpper() == email.ToUpper());'.

    Expected result:
    Boolean value.

    Real result:
    Exception "Failure: Count(*) query failed: Telerik.OpenAccess.RT.sql.SQLException: ORA-00932: inconsistent datatypes: expected - got CLOB".

    TDA generated wrong SQL query 'SELECT COUNT(1) FROM ( SELECT a."Id" COL1, a."Email" COL2 FROM "Users" a WHERE upper(a."Email") = ?'. Right query is: 'SELECT COUNT(1) FROM ( SELECT a."Id" COL1, a."Email" COL2 FROM "Users" a WHERE DBMS_LOB.COMPARE(upper("Login"), ?) = 0'.

    Will you change comparison generation for Oracle CLOB columns?

  2. Ralph Waldenmaier
    Ralph Waldenmaier avatar
    202 posts

    Posted 14 Oct 2014 Link to this post

    Hi Constantine,
    The DBMS_LOB.COMPARE function call is necessary to ensure that CLOB types are compared correctly on the server. Unfortunately, in this situation the UPPER function call does not return the appropriate type to perform the operation correctly.

    Though there is a way to fullfil your requirements by leveraging the .SQL LINQ extension. Please see this blog post for an example.

    In your case it would look like this.
    database.Users.Any(u => "UPPER({0}) = UPPER({1})".SQL<bool>(u.Email, email));

    Please double check if the UPPER really meets your requirements because the documentation is not 100% clear if the same amount of data is returned as stored in the CLOB. This means, a CLOB can have a length bigger than 4000 chars. Probably the UPPER returns a max of 4000 chars.

    I hope this information is helpful for you.
    Do come back in case you need further assistance.

    Ralph Waldenmaier
    OpenAccess ORM is now Telerik Data Access. For more information on the new names, please, check out the Telerik Product Map.
Back to Top