If I have an Informix DDL script for a database and I want to run this script on Oracle to create the equivalent database with the same structure, indexes, data types and constraints.
What are the steps I should follow to execute the script successfully and what are the factors I should take into consideration during the execution?
- I use Informix server version IBM Informix Dynamic Server Version 12.10.FC3.
- I use Informix Client SDK version 3.50.
- I use Oracle 11g.
Jonathan Leffler :
It is a non-trivial proposition, in general, to transfer even just DDL between Informix and Oracle. There are numerous detailed differences in the syntax, even if you don't use some of the more exotic types (e.g. user-defined types, or lists or sets or row types). This is written from an Informix perspective; treat statements about Oracle with a mild pinch of salt.\n\n\nOracle uses VARCHAR2; Informix doesn't (but some types that Informix use should not be translated to Oracle's VARCHAR — it should be VARCHAR2).\nOracle has one underlying numeric type; Informix has many. However, Oracle recognizes most of the type names.\nOracle's DATE type includes a time component; Informix's does not.\nInformix has esoteric types such as DATETIME MONTH TO MINUTE (the canonical odd-ball example) which don't have a ready equivalent in Oracle.\nYou'll need to scrutinize BYTE, TEXT, BLOB and CLOB types carefully and translate accordingly.\nThere are probably differences in the limits on some of the types. These may cause issues in translation.\nThere are nitpicking differences between Informix and the rest of the world in the naming of constraints (Informix puts the constraint name after the constraint; the standard puts the constraint name before it).\n…and no doubt many other problems…\n\n\nThere are tools available to assist with migrations from Oracle to Informix. I assume there are tools available to assist with the reverse migration, but I am not familiar with them.",
2016-01-05T15:29:53