ORA-00932: inconsistent datatypes: expected DATE got BINARY in Hibernate

8
votes

My query is like this:

where (:startDate is null or :endDate is null or DDATE between :startDate AND :endDate)
AND (:startDate is null or (:endDate is not null or DDATE between :startDate AND :date))

I get startDate and endDate from ajax date picker. date is the system date, which I am getting like this:

Date utiDate = new Date();

When I execute my query, I get the error:

java.sql.SQLException: ORA-00932: inconsistent datatypes: expected DATE got BINARY
2
javasqloraclehibernate
Just to be sure the problem is there: change :date with :enddate. You won't have the correct result, but you'll be sure that the problem is in the type of :date and not in :startdate or :enddate. You should obtain a result or a different exception. - eternay
A guess would be the nested condition (:endDate is not null or VDDATE between :startDate AND :date). Have you tried running it without the nested brackets? - howiewylie
@eternay I replaced it with startdate as u suggested but i still get the same error. - BaN3
@howiewylie: I removed the nested condition and still the same error occurs. - BaN3
Sorry, the error code (ORA-xxx) shows that it's an Oracle database. Just forget my previous comment. - eternay

2 Answers

9
votes

Trying to find the correct answer, I found an interesting post here.

If :endDate is null, you can't be sure the condition DDATE between :startDate AND :endDate won't be evaluated. And if it's evaluated, Oracle will try to convert a null value to a date, so it'll give you an error.

Try to test the query removing the DDATE between :startDate AND :endDate part: you shouldn't have the error anymore. Then you'll have to modify your query to be sure the between operator won't be evaluated if :enddate is null. In this post, they recommend using CASE statements inside the WHERE clause. Maybe it can solve your problem.

About short-circuit evaluation and Oracle database, I found that question that can help you understand the problem.

2
votes

Properties :startDate and :endDate in your bean are not of type java.util.Date, but something else that Hibernate can't convert to SQL-DATE automatically and is sending BINARY as default.

You need to implement javax.persistence.AttributeConverter< X, Y >

There is some tutorial on that.

Hint: if you place that converter in some jar included in your war app, you must explicitly list it in persistemce.xml, otherwise your converter will not be found:

    <persistence-unit name="default" transaction-type="JTA">
        <jta-data-source>java:/mydb</jta-data-source>

        <!-- register converter -->
        <class>com.example.persistence.LocalDateTimeConverter</class>
        ...