Syntax error with Oracle specific SQL

22 views

Skip to first unread message

Ferenc Gratzer

unread,

Feb 28, 2020, 8:43:25 AM2/28/20

to H2 Database

Hi,

Two Oracle specific SQLs generated from Enterprise Architect cause syntax error in H2 (tried with version 1.4.199 and 1.4.200). They must be unsupported at the moment.
However, I think these SQL should be parse-able and accepted as valid SQL.

1) NOORDER in CREATE SEQUENCE:

Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "CREATE SEQUENCE ""SEQ_SYSTEM_ACTIVITY_ID""

INCREMENT BY 1

START WITH 1

NOMAXVALUE

MINVALUE 1

NOCYCLE

CACHE 20

NOORDER[*]"; SQL statement:

CREATE SEQUENCE "SEQ_SYSTEM_ACTIVITY_ID"

INCREMENT BY 1

START WITH 1

NOMAXVALUE

MINVALUE 1

NOCYCLE

CACHE 20

NOORDER [42000-200]

at org.h2.message.DbException.getJdbcSQLException(DbException.java:453)

at org.h2.message.DbException.getJdbcSQLException(DbException.java:429)

at org.h2.message.DbException.get(DbException.java:205)

at org.h2.message.DbException.get(DbException.java:181)

at org.h2.message.DbException.getSyntaxError(DbException.java:229)

at org.h2.command.Parser.getSyntaxError(Parser.java:1051)

at org.h2.command.Parser.prepareCommand(Parser.java:741)

at org.h2.engine.Session.prepareLocal(Session.java:657)

at org.h2.engine.Session.prepareCommand(Session.java:595)

at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1235)

at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:212)

at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:201)

at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:352)

If NOORDER is removed, the SQL file is processed successfully.

2) USING INDEX in ALTER TABLE:

Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "ALTER TABLE ""SYSTEM_ACTIVITY""

ADD CONSTRAINT ""SYSTEM_ACTIVITY_PK""

PRIMARY KEY (""ID"")

USING[*] INDEX"; SQL statement:

ALTER TABLE "SYSTEM_ACTIVITY"

ADD CONSTRAINT "SYSTEM_ACTIVITY_PK"

PRIMARY KEY ("ID")

USING INDEX [42000-200]