Informix to Oracle Migration

Hi,
  I am sure if anyone can help me, you guys can!

  I am an Informix Developer, moving to Oracle 8i and later databases. I am struggling to find the Oracle equivelant to many of the useful Informix features that I have come to rely upon.

  I have looked through the Oracle/Infomix groups and some helpful users have posted some solutions, but many of them seem to be very complex work-arounds for features that were readily available in Informix.

  Let me give you some examples:

1. We regularly used the Informix LOAD FROM and UNLOAD TO statments e.g.

    UNLOAD TO '/tmp/orderdata.unl'
    SELECT * FROM ORDER
    WHERE val > 100000;

    There does not seem to be an easy way to do this in Oracle. Am I missing something ( I am sure the answer to that is "Yes"! )

  2) Informix has a serial data type, so if you had a primary key that you would like to auto-increment, you declare it as serial, all done. I searched groups and the solution seems to be to create a sequence and on insert triggers. Seems a long workaround to me. Is this really the recommended way of implementing this behavior in Oracle?

    If, during testing, you wish to unload some of the tables to file and reload them when the tables have been dropped and re-created, it seems to be a struggle, as the sequence will generate new key values for all the records on insert, and referential integrity is broken. Informix's serial datatype does not have a problem with this, if you insert a row with a specific value into a serial column, and the value is unique, it inserts that value rather than a new serial value, so loading old data back into the table is not a problem, integrity in maintained.

  3) Any user other than the table owner must prefix the table name in queries with the owner name, e.g. DEVUSER.ORDER. Why is this? Would any sensible database design allow for two identically named tables in the same schema, but with different owners? I do appreciate that synonyms can be created to work around this, but again this seems to be a workaround for a problem that just creates extra hassle and no benefit.

  I am sure you guys can help me out with this stuff, it would make my professional life a hell of a lot easier if I felt with Oracle, like I did with Informix, that I am being assisted by the database, not that it is my greatest enemy to hitting deadlines.

Thanks in advance

PH Received on Fri Jan 23 2004 - 09:21:31 CET