Any idea on H2 (Oracle MODE) "Syntax error : SELECT NEXTVAL FROM[*] DUAL"?

I have the following error using H2 (v1.3.154) with Oracle mode :

<bean id="datasource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="org.h2.Driver" />
        <property name="url" value="jdbc:h2:mem:testdb;MODE=Oracle" />
        <property name="username" value="sa" />
        <property name="password" value="" />
</bean>

and Oracle dialect for hibernate v3.5.6 :

hibernate.dialect=org.hibernate.dialect.Oracle10gDialect

The error :

Caused by: org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement "SELECT 
SQ_PERSON_ID.NEXTVAL FROM[*] DUAL "; expected "identifier"; SQL statement:
select SQ_PERSON_ID.nextval from dual [42001-154]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:327)
at org.h2.message.DbException.get(DbException.java:167)
at org.h2.message.DbException.getSyntaxError(DbException.java:192)
at org.h2.command.Parser.readColumnIdentifier(Parser.java:2752)
at org.h2.command.Parser.readTermObjec开发者_如何学GotDot(Parser.java:2311)
at org.h2.command.Parser.readTerm(Parser.java:2428)
at org.h2.command.Parser.readFactor(Parser.java:2025)
at org.h2.command.Parser.readSum(Parser.java:2012)
at org.h2.command.Parser.readConcat(Parser.java:1985)
at org.h2.command.Parser.readCondition(Parser.java:1850)
at org.h2.command.Parser.readAnd(Parser.java:1831)
at org.h2.command.Parser.readExpression(Parser.java:1823)
at org.h2.command.Parser.parseSelectSimpleSelectPart(Parser.java:1736)
at org.h2.command.Parser.parseSelectSimple(Parser.java:1768)
at org.h2.command.Parser.parseSelectSub(Parser.java:1663)
at org.h2.command.Parser.parseSelectUnion(Parser.java:1508)
at org.h2.command.Parser.parseSelect(Parser.java:1496)
at org.h2.command.Parser.parsePrepared(Parser.java:401)
at org.h2.command.Parser.parse(Parser.java:275)
at org.h2.command.Parser.parse(Parser.java:247)
at org.h2.command.Parser.prepare(Parser.java:201)
at org.h2.command.Parser.prepareCommand(Parser.java:214)
at org.h2.engine.Session.prepareLocal(Session.java:426)
at org.h2.engine.Session.prepareCommand(Session.java:374)
at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1100)
at org.h2.jdbc.JdbcPreparedStatement.<init>(JdbcPreparedStatement.java:71)
at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:243)
at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:534)
at org.hibernate.jdbc.AbstractBatcher.prepareSelectStatement(AbstractBatcher.java:145)
at org.hibernate.id.enhanced.SequenceStructure$1.getNextValue(SequenceStructure.java:106)
... 81 more

Any idea of what happens, a miss in my config ? I found this post and try the patch, it does not resolve the error. Thanks for your help !

Please ensure the sequence is created. If it is created, then it works for me:

create sequence SQ_PERSON_ID;
select SQ_PERSON_ID.nextval from dual;

If it is not created, then the same error message is thrown as you got.

I was working on h2 with Oracle mode but all the above solutions mentioned above didn't work for me. Although after some research I found that this query will work fine for fetching the next value in the sequence.

select nextval('SchemaName', 'SequenceName');

As @longliveenduro mentioned

It's definitely the H2 way to say "Sequence does not exist".

I had the exact same problem and found out that the sequence was not being created in h2's in-memory DB. I solved the problem by adding:

CREATE SEQUENCE SQ_PERSON_ID
MINVALUE 1
MAXVALUE 9223372036854775807
START WITH 1
INCREMENT BY 1
CACHE 8
NOCYCLE;

in: test/resources/schema.sql

It worked for me.

Check if you use the same schema under which the sequence is created. If not, insert a schema prefix before sequence name, such is MYUSER.MY_SEQ.

Aside from any possible Hibernate issue, does your SA user have select permission against the sequence, which seems to be in a different schema? (See example in documentation).

Just to add one more point, h2 is case sensitive unlike oracle. so DDL and java code should at least use same case for sequence name