The LOAD and UNLOAD instructions

Informix^® provides two SQL instructions to export / import data from / into a database table: The UNLOAD instruction copies rows from a database table into a text file and the LOAD instructions insert rows from a text file into a database table.

ORACLE does not provide LOAD and UNLOAD instructions, but provides external tools like SQL*Plus and SQL*Loader.

Solution

In BDL programs, the LOAD and UNLOAD instructions are supported with ORACLE, with some limitations:

There is a difference when using ORACLE DATE columns. DATE columns created in the ORACLE database are equivalent to Informix DATETIME YEAR TO SECOND columns. In LOAD and UNLOAD, all ORACLE DATE columns are treated as Informix DATETIME YEAR TO SECOND columns and thus will be unloaded with the "YYYY-MM-DD hh:mm:ss" format.
The same problem appears for Informix INTEGER and SMALLINT values, which are stored in an ORACLE database as NUMBER(?) columns. Those values will be unloaded as Informix DECIMAL(10) and DECIMAL(5) values, that is, with a trailing dot-zero ".0".
When using an Informix database, simple dates are unloaded using the DBDATE format (ex:"23/12/1998"). Therefore, unloading from an Informix database for loading into an ORACLE database is not supported.