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 !

It's definitely the H2 way to say "Sequence does not exist". Had the same problem with this misleading error message. — Chris W., Oct 19 '11 at 16:17

score 23 · Accepted Answer · answered Apr 26 '11 at 12:20

23

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.

answered Apr 26 '11 at 12:20

Thomas Mueller

48,905
14
116
132

score 5 · Answer 2 · answered Aug 30 '18 at 07:38

5

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

answered Aug 30 '18 at 07:38

Ashwani Sharma

439
1
8
9

thanks! This works.. I was stuck at the same thing and none of the above solutions worked.. – user1673567 Jan 11 '19 at 05:18

score 4 · Answer 3 · answered Nov 02 '19 at 10:42

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.

score 2 · Answer 4 · answered Dec 03 '13 at 11:09

2

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.

answered Dec 03 '13 at 11:09

voho

2,805
1
21
26

score 0 · Answer 5 · answered Apr 26 '11 at 09:59

0

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

answered Apr 26 '11 at 09:59

Alex Poole

183,384
11
179
318

I don't think so since it is a syntax error, not a permission denied or table not found... – Jerome VDL Apr 26 '11 at 10:50
1

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`). – Alex Poole Apr 26 '11 at 11:16

score 0 · Answer 6 · answered Mar 15 '21 at 10:36

0

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

answered Mar 15 '21 at 10:36

sigirisetti

329
2
10

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

6 Answers6