Migrating to Oracle

This is a good reason for either only using standard SQL as much as possible, or for isolating those dependencies into stored procedures (yes, I know that doesn't help you in this specific case, I just thought I'd mention it for future reference).

I suspect you'll have to change each one individually although a simple search over you source code for "select " or "first " will be a good start.

Then you can decide how you want to change them, since you may also still want it to work on Informix.

For what it's worth, I think you get the same effect with Oracle's

select * from ( select * from mytable ) where rownum <= 10

I would be farming the job of dynamically constructing a query (based on a template) out to another layer which can return a different query based on which database you have configured. Then, when you also want to support DB2 (for example), it's a simple matter of changing just that layer.

For example, have a call like:

gimmeRowLimitedSqlQuery ("* from test",10);

which would give you either of:

select first 10 * from testselect * from test where rownum <= 10

I should also mention, although I realise your query is just an example, that SQL can return rows in any order if you don't specify order by so

select first 10 * from test

makes little sense, especially if you may be running it in different DBMS'.

You could write an extension to the JDBC driver to modify the queries on the fly but probably that is an overkill so a careful search and replace on the source code to modify all queries would be more appropriate.

Oracle has the concept of ROWNUM for limiting results. You will have to update your queries for this.