HSQLDB ROWNUM compatibility with Oracle

Question

THe HSQLDB changelog states that ROWNUM() was added in v2.2.0 which I am using without any problems when running integration tests against the in-memory HSQLDB.

However I want to run the same tests against a real Oracle 10g database, but the query fails because the pseudo-column is called ROWNUM. Is there an easy way write a single query string that works in both environments?

score 21 · Accepted Answer · answered Jul 07 '11 at 15:40

21

The ROWNUM() function is available by default in HSQLDB 2.2.x and later. If you enable Oracle syntax compatibility mode, you can also use ROWNUM.

This statement enables it:

SET DATABASE SQL SYNTAX ORA TRUE

Or use the connection property sql.syntax_ora=true

answered Jul 07 '11 at 15:40

fredt

24,044
3
40
61

Perfect! I did not know that existed :-) – andyb Jul 07 '11 at 15:48
I just found a related question http://stackoverflow.com/questions/4579078/how-to-use-hsqldb-in-oracle-query-syntax-mode – andyb Jul 07 '11 at 16:06
1

Note that the `sql.syntax_ora=true` must be given in the connection URL: e.g. ``, not in a `` – Julien Kronegg Oct 03 '19 at 07:19

HSQLDB ROWNUM compatibility with Oracle

1 Answers1

Linked