HSQLDB ROWNUM compatibility with Oracle

oracle hsqldb rownum
andyb picture andyb · Jul 7, 2011 · Viewed 7.4k times · Source

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

fredt picture fredt · Jul 7, 2011

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

Related questions

Unit test MyBatis with HSQL instead of Oracle

I would like to unit test my MyBatis persistence layer using an HSQL in-memory database. The real application uses an Oracle database. This worked fine unitl we started adding auto incremented numbers for the id columns. Oracle requires the use …

Read More
Get list of all tables in Oracle?

How do I query an Oracle database to display the names of all tables in it?

Read More
How do I limit the number of rows returned by an Oracle query after ordering?

Is there a way to make an Oracle query behave like it contains a MySQL limit clause? In MySQL, I can do this: select * from sometable order by name limit 20,10 to get the 21st to the 30th rows (skip the …

Read More