Migrating to Oracle

Question

We are using a JEE application and we are right now using Informix DB.Our code hits the DB with queries like "select first 10 * from test" Now as far as I know Oracle does not support 'first 10 *' kind of statements.We have more than 1000 queries like this.Should we manually change this or can have some manual customization?

Answer 1

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.

Answer 2

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 test
select * 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'.

Answer 3

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

Answer 4

TOP-n and Pagination queries are a little bit more complex than just using ROWNUM. For example, you might be surprised that you don't get the expected results when using ROWNUM with ORDER BY in the same query.

Check http://www.oracle.com/technology/oramag/oracle/07-jan/o17asktom.html for more info on those type of queries in Oracle.