8

I want to make dynamic query in which if particular parameter is sent, the Native query should filter the result based on it. In case it's null, it should not reflect the result.

I am using Spring Data JPA with Native query mechanism + Oracle DB

For String parameters this approach works fine

:email is null or s.email = :email

but for Integer parameters when they have value, the Query works but if the parameter is null the query fails with the error

Caused by: java.sql.SQLSyntaxErrorException: ORA-00932: inconsistent datatypes: expected NUMBER got BINARY

I am using the exactly the same approach for for Integer instead of String

I am wondering whether the problem is on my side or it's some kind of bug?

Tsvetoslav Tsvetkov
  • 954
  • 10
  • 24
  • Does this answer your question? [java.sql.SQLException: ORA-00932: inconsistent datatypes: expected NUMBER got BINARY](https://stackoverflow.com/questions/23218102/java-sql-sqlexception-ora-00932-inconsistent-datatypes-expected-number-got-bi) – Harmandeep Singh Kalsi Jul 16 '20 at 16:01
  • Thank you for the response. No, it's different kind of problem producing the same error. In my case I expect the parameter to be as a "null" value. This topic is some kind of related with my problem but the proposed solutions work only for String parameter, not for Integer - https://stackoverflow.com/questions/43780226/spring-data-ignore-parameter-if-it-has-a-null-value – Tsvetoslav Tsvetkov Jul 16 '20 at 16:16

1 Answers1

10

In Oracle DB it's not worked this way. A workaround is using JPQL like

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

Or for native query use TO_NUMBER function of oracle

SELECT s FROM Entity s WHERE :id is null OR s.id = TO_NUMBER(:id)
Eklavya
  • 17,618
  • 4
  • 28
  • 57
  • Thank you very much @Eklavya. Your solution works as expected. Can you explain please why when the parameter has value, for example Integer age = 1; the query work but when the value is null, for example Integer age = null; it fails? – Tsvetoslav Tsvetkov Jul 17 '20 at 07:07
  • @Tsvetoslav Here you find details for that https://dba.stackexchange.com/questions/4291/why-does-null-not-work-in-oracle – Eklavya Jul 17 '20 at 07:23