1

I made a dbexport from Informix database and I have the .sql and .unl files. Can I recreate the exported database into Oracle from the files?

If not is possible, is there any RDBMS that can do it?

Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
pacobm
  • 45
  • 6
  • 2
    I don't know what facilities Oracle provides, but it is likely there's an off-the-shelf solution to import such data. However, the issue you face include: (1) Data format — pipe-separated fields is probably not the default in Oracle; (2) Data types — you may be using types in Informix without a good analogue in Oracle; (3) SQL syntax — you may be using features of Informix that Oracle doesn't support; (4) Stored procedures — Informix SPL (stored procedure language) is very different from Oracle's, so transferring stored procedures may be tricky. – Jonathan Leffler Apr 03 '17 at 15:32
  • 2
    You'll need to review the `.sql` file and the `.unl` files (mostly the `.sql` file) to see the scope of the problems you face. – Jonathan Leffler Apr 03 '17 at 15:33

1 Answers1

0

As Jonathan wrote in comment it will not be easy. Files created by Informix dbexport are not directly usable by other databases. But while this is easy to read text information you can write program that converts both .sql and .uml files into files usable to your preferred database. If you use stored procedures then it is really hard.

For table definitions in CREATE TABLE ... you will have to convert Informix types like DATETIME YEAR TO DAY to DATE, or SERIAL into INTEGER. Oracle have no SERIAL type so you will have to create sequence and trigger for such columns.

When you convert db schema you can import data from .unl files. I suggest to write your own program that reads it as CSV data, make PrepareStatement with INSERT and converts some data. Also remember that Oracle do not use empty strings: null vs empty string in Oracle

Community
  • 1
  • 1
Michał Niklas
  • 53,067
  • 18
  • 70
  • 114
  • Fortunatelly I don't have any stored procedure, I only have database base definition and data. To solve the problems of data types that Jonathan points, I think that replacing in the sql file those types for the corrects in Oracle would be a solution. – pacobm Apr 05 '17 at 06:43