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

h2 runscript
h2 ifexists=true
h2 oracle dialect
h2:file database
h2 database embedded java example
h2 server
h2 cache_size
h2 sequence

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 !

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.

Using H2 and Oracle with Spring Boot, For H2 we need to override four properties. We need to tell H2 to start in its Oracle compatibility mode by setting this option in the JDBC url for H2. Set the datasource platform to 'H2'. Override the default behavior of Hibernate to not create and drop the database. Allow Hibernate to continue on error. It is very easy and well documentented how to run H2 in server mode. Just code: Server.createTcpServer().start(); Very easy. But I'm unable to find an answer on: how to give the created database

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.

Features, H2 in Use Connection Modes Database URL Overview Connecting to an Embedded (Local) Database In-Memory Databases Database Files Encryption Specifically, I have this query that returns the expected number of records in oracle but zero in h2 despite the records being there: SELECT count(*) from click_impressions WHERE impression_date BETWEEN (sysdate - 1/24) AND (sysdate); Any ideas? I have enabled "SET MODE Oracle" as well.

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

Tutorial, An icon will be added to the system tray: H2 database icon When using the H2Dialect , compatibility modes such as MODE=MySQL are not supported. In the application, an idea is to use one connection per Session, or even one  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.

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.

Information Security and Cryptology: Third SKLOIS Conference, , If H2(m, rp) has been defined, then abort(a collision appears). In the random oracle mode, let Fo be a polynomial-time adversary who manages an Exp'”(k)  syntax to query a value-based collection element forum.hibernate.org. Can someone please post (or point me to the docs) the correct syntax for querying a value-based collection which is a property of an object? I've searched the documentation, HIA, google and I just can't find it! elements() won't work for me because I'm using MySQL.

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

[FEATURE] H2 DB URL should include MODE=xxx · Issue #3841 , Usually, this operation is not allowed and an exception is thrown. To use the Oracle mode, use the database URL jdbc:h2:~/test;MODE=Oracle or the SQL That might be a good idea to set based on the prod db choosen. When I run my app in non-test mode (which uses mysql) it works fine, but when I run my unit-test using in-memory H2 database, I get the following error: org.h2.jdbc.JdbcSQLException: Column "ID" not found; SQL statement:

Pairing-Based Cryptography, As soon as M submits such an entry to the H2 oracle, B aborts the game and B changes the mode of operation of the H1 oracle so that H1 operates as in Table  By "mode", I mean the most frequently occurring value for salary within each department. I know I can do this with a self join, but I'm looking for an analytic solution (for performance evaluation). Pretty basic, but I haven't been able to find the answer - the word "mode" is too common.

Transition from Oracle to H2, specific functions when running in Oracle mode. Maybe http://www.jetbrains.​com/idea There is already an oracle compatibility mode for H2. ORACLE_HOME + ORACLE_SID = key used to attach to shared memory. if the db was started with an oracle home of /a/b/c -- then trying to connect to it with /a/b/c/ won't work. you need to make sure the oracle home you use, is the oracle home used to start

Computational Intelligence and Security: International Conference, , Note: An ordinary signing oracle is not provided, because it can be simulated by a In the random oracle mode, let F0 be an adversary which has running time T system parameters G2, ˆe, Ppub ,Pa ,H1 ,H2, where Ppub = sP, Pa = sa P, 2. Followup . December 19, 2002 - 7:11 am UTC . You want to have to manually kick -- you have to sync them up anyway (eg: if this remote archival location is to be of any use, after a failure, you need to manually intervene in order to get the missed logs over there -- else, if it "just fixed itself" and started archiving again, well, you would be missing archives there.

Comments
  • It's definitely the H2 way to say "Sequence does not exist". Had the same problem with this misleading error message.
  • thanks! This works.. I was stuck at the same thing and none of the above solutions worked..
  • I don't think so since it is a syntax error, not a permission denied or table not found...
  • 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).