How can I query a null in a Long value without getting "Expected NUMBER but got BINARY" from OracleDB?

I don't know the specifics of JPQL nor how Oracle handles the WHERE condition of your query. But I'd bet that the second part of your WHERE condition is not completely ignored and that a.id = NULL is causing the problem. Apart from the apparently inconsistent data types a condition like some_value = NULL may not evaluate to TRUE or FALSE but to NULL (at least this happens on PostgreSQL).

EDIT
For your specific use case the combined condition :id IS NULL OR a.id = NULL still works as intended on PostgreSQL. But in another context you won't get any rows with some_value = NULL even if some_value is null. So I think for the sake of robust and understandable code an expression like some_value = NULL should be avoided in any case.
END EDIT

You might be able to work around the problem in JPQL with

SELECT a FROM Auftrag a WHERE :id is null OR a.id = COALESCE(:id, -1)

at least this is possible with native Hibernate HQL. In this case the second part of the WHERE condition evaluates to FALSE if :id is null but the whole WHERE condition evaluates to TRUE, which is what you want.

But for dynamic filtering queries a better approach would be to use the JPA 2.0 Criteria API and include the :id parameter in the query only if it is not null. Again, I don't know the specifics of JPA Criteria but with native Hibernate Criteria this would be

public List<Auftrag> findByFilter(Long id) {   Criteria criteria = session.createCriteria(Auftrag.class);  if (id != null) {    criteria.add(Restrictions.eq("id", id));  } // if  return criteria.list();}

Hope that helps.

In Oracle (12), i found a workaround using TO_NUMBER:

SELECT a FROM Auftrag a WHERE :id is null OR a.id = TO_NUMBER(:id)

I've had the same problem, I solved it by reversing OR sides, for example:

SELECT a FROM Auftrag a WHERE :id is null OR a.id = :id

didn't work, but reversing OR sides like this:

SELECT a FROM Auftrag a WHERE a.id = :id OR :id is null

worked perfectly. I don't understand why, but it works. It probably has something to do with "short circuiting" but in case of null both statements are evaluated anyway. Hope someone can explain this.