1

I've got following formula in my entity:

@Formula("FLOOR(sysdate - last_date)")
private Long daysSinceLastDate;

In application I'm using Oracle DB and it's working flawlessly, calculating the days. Hovewer, when running integration tests on embedded H2 database (with Oracle compatibility) I've got an error:

Syntax error in SQL statement ... expected "identifier";
select {...}
   FLOOR(projaudit0_.sysdate - projaudit0.last_date) as formula0_
from proj.AUDIT projaudit0_

When I'm changing the code to

@Formula("FLOOR(sysdate() - last_date)")
private Long daysSinceLastDate;

It's working in tests, but in running application Oracle reports "Missing right parenthesis".

I've tried also CURRENT_TIMESTAMP and CURRENT_DATE, but it's not working.

EDIT: I asked for reopening, since connected answer doesn't work. CURRENT_TIMESTAMP() is recognized as H2 function and Oracle function, but my last_date is of type DATE, which means it cannot be compared:

Caused by: java.sql.SQLException: ORA-30088: datetime/interval precision is out of range
mdziob
  • 1,116
  • 13
  • 26
  • Interesting. Oracle will definitely not allow you to call `sysdate()` (with parentheses) even though `sysdate` is a function. It is interesting that you say "it's working in tests" - I wonder what you mean by that. The problem, with the original call, is that apparently your wrapper application automatically adds a qualifier (schema name? what is projaudit0?) where it's not needed - and that breaks the proper call to `sysdate`. Doesn't seem like an Oracle issue. –  Dec 09 '19 at 19:39
  • @mathguy, sysdate() is working in integration tests on embedded H2 database with Oracle compatibility mode. Looks like H2 thinks, that sysdate is name of column from this table, and tries to add it to select, but it's not there. – mdziob Dec 09 '19 at 20:01

0 Answers0