0

Im using SPRING JPA in my application and trying to run a native query as follows:

@Query (value="select max(ts) from abc.test s         \n" + 
                        "where abc.getTest(s.user_id)  = :#{#userId}            \n" + 
                        "and upper(app_name) = 'TAX' and INSTR(s.user_id, '.') > 0        \n" + 
                        "group by user_id, app_name", nativeQuery=true)
Timestamp getLastLogin(BigDecimal userId);

I am getting exception as follows:

Caused by: Error : 932, Position : 110, Sql = select max(ts) from abc.test s         
where ac.getTest(s.user_id)  = :1             
and upper(app_name) = 'TAX' and INSTR(s.user_id, '.') > 0        
group by user_id, app_name, OriginalSql = select max(ts) from abc.test s         
where abc.getTest(s.user_id)  = ?            
and upper(app_name) = 'TAX' and INSTR(s.user_id, '.') > 0        
group by user_id, app_name, Error Msg = ORA-00932: inconsistent datatypes: expected NUMBER got BINARY

    at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:514)
    ... 109 more
<org.hibernate.engine.jdbc.spi.SqlExceptionHelper> <SqlExceptionHelper> <logExceptions> <SQL Error: 932, SQLState: 42000> 
<org.hibernate.engine.jdbc.spi.SqlExceptionHelper> <SqlExceptionHelper> <logExceptions> <ORA-00932: inconsistent datatypes: expected NUMBER got BINARY
> 

I tried to convert the max(ts) to string using to_char and changed the coresponding getter/setter to String still getting the same issue.

Update: I fixed it with this small change

String getLastLogin(@Param("userId") BigDecimal userId); Didnt add @Param for the arguments.

2
  • 1
    What oracle type does the function abc.getTest return? Commented Jun 25, 2021 at 12:40
  • @SternK thank you. I have edited my post with the fix for the issue Commented Jun 25, 2021 at 18:15

1 Answer 1

2

That query return null value thats why that null values can not assign to number. We can not assign null to int(primitive) in java.

Sign up to request clarification or add additional context in comments.

1 Comment

thank you. I have edited my post with the fix for the issue

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.