Search code examples
oracle-databasehsqldbrownum

HSQLDB ROWNUM compatibility with Oracle

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?

Solution

  • 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