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?

Answer 1

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