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

Question

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

Solution 1

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.

Solution 2

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>
        ...

Comments

[BaN3]

Ur answer helped me i did it using stringbuilder like this ` if(startDate!=null) if(endDate!=null) temp.append("AND (TRS_RCVDDATE between :startDate AND :endDate) "); if(startDate!=null) if(endDate==null) temp.append("AND (TRS_RCVDDATE between:startDate AND :date) ");`