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

Question

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.readTermObjectDot(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 !

Pease

It's definitely the H2 way to say "Sequence does not exist". Had the same problem with this misleading error message. – Corrosion

Answer 1

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.

Ardussi

Answer 2

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');

Sitology

thanks! This works.. I was stuck at the same thing and none of the above solutions worked.. – Pennington

Answer 3

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.

Lowercase

Answer 4

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.

Nellynelms

Answer 5

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).

Spithead

I don't think so since it is a syntax error, not a permission denied or table not found... – Pease

My reading of the post you linked to is that H2 doesn't recognise the sequence name and is generating the identifier message, you aren't getting as far as an Oracle error. But I'm not familiar with this, or how it works under the hood. Assuming the schema the sequence belongs to is included in your schema search path, I'd still check you can see it through SQL*Plus etc. when connected as SA. (As a very naive guess, perhaps H2 is doing the equivalent of looking in ALL_OBJECTS or ALL_SEQUENCES). – Spithead

Answer 6

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

Aylsworth