Migrating to Oracle

Question

We are using a Java EE 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

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'.