SpringBoot oracle.jdbc.OracleDatabaseException: ORA-00932: inconsistent datatypes: expected DATE got NUMBER

Question

2019-03-01 16:38:44.930 WARN 55052 --- [nio-8080-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper : SQL Error: 932, SQLState: 42000

2019-03-01 16:38:44.930 ERROR 55052 --- [nio-8080-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper : ORA-00932: inconsistent datatypes: expected DATE got NUMBER

2019-03-01 16:38:44.946 ERROR 55052 --- [nio-8080-exec-1] o.a.c.c.C.[.[.[/].[dispatcherServlet] : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet] with root cause

oracle.jdbc.OracleDatabaseException: ORA-00932: inconsistent datatypes: expected DATE got NUMBER

Query that I want eventually, I guess just not correctly formed using spring CrudRepository:

select * from user_usage where Prs_Id=1104438622 and createddate > sysdate -1;

desc user_usage

Name          Null     Type           
------------- -------- -------------- 
USAGETYPEID   NOT NULL NUMBER(3)      
PRS_ID        NOT NULL NUMBER(18)     
CREATEDID     NOT NULL NUMBER(10)     
CREATEDDATE   NOT NULL DATE           
COMMENTS               VARCHAR2(4000) 
USERAGENTID            NUMBER(10)

1)POJO

    @Entity
    @Table(name="USER_USAGE)
    public class Usage {

        @Column(name="Prs_Id")
        @Id
        private long prsId;

        @Column(name="createddate")
        private Date createddate;

2)Repository

    public interface UsageRepository extends CrudRepository<Usage, Date>{


        //select * from user_usage where Prs_Id=1104438622 and createddate > sysdate -1; --query that I want eventually 
         @Query("SELECT a FROM Usage a WHERE a.prsId=:prsId and a.createddate>=:createddate-1")
         Usage fetchUsageGreaterThanEqual(@Param("prsId") Long prsId, @Param("createddate") Date createddate);
    }

3)Controller :

    @Autowired
    UsageRepository usageRepository; 
    static Date myDate;

    @GetMapping("/{prsId}")
     public Usage getUsageByPrsId(@PathVariable Long prsId) {
      return usageRepository.fetchUsageGreaterThanEqual(prsId, myDate);
     }

@Deadpool, I am trying to fetch data since yesterday so subtracting from current date, any other workaound not much familiar with CRUD methods ? — vicky
@Anil, as its a ORA error, its coming from Oracle due inconsistent datatypes from java layer. — vicky

Marmite Bomber Marmite Bomber · Accepted Answer · 2019-03-03T15:12:23

Your Date class is most probably java.util.Date

static Date myDate;

You must convert it to java.sql.Date as described here

new java.sql.Date(createddate.getTime())

to avoid the problem with ORA-00932: inconsistent datatypes: expected DATE got NUMBER

Alternatively you may also use the java.time classes as discussed here and there

SpringBoot oracle.jdbc.OracleDatabaseException: ORA-00932: inconsistent datatypes: expected DATE got NUMBER

1 Answers