How Informix data is imported into the Oracle database

Source: Internet
Author: User
Tags informix import database perl script


Different types of database interoperability data, due to different database mechanisms, can not be achieved through simple copy and paste, but also due to different file formats, can not simply export files, import database.

About Informix Export data, import to Oracle method, after experiment can be used as follows: (although a bit stupid, but for small-volume requirements, can work ^_^)

(Once used Perl script implementation, but because of debugging Perl environment and the dependency of PL a bit of trouble, failed to pass smoothly, the stupid method is recorded as follows)

1. Export data to UNL file format in Informix

Unload to '/INFORMIX/SYSTEM_SETUP20150617.UNL ' select * from System_setup;

Dbschema-d nmosdb-t Tfa_alarm_relation–ss

2 Making CTL control files Circuit.ctl

Load data

infile ' CIRCUIT20150610.UNL ' --exported unl file

into table circuit --Imported table name

Fields terminated by ' | ' --Separators

TRAILING nullcols --List all fields in a table

(Object_class char (10000),

Resourcesid Char (10000),

int_id Char (10000),

Circuitno Char (10000),


Bnodeport_rdn Char (10000),

Localnode_rdn Char (10000),

Romotenode_rdn char (10000))

3 Configure the environment variables that the SQLLDR command can use

User Environment variables:






Oracle Client Configuration:


ORCL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = (PORT = 1521)) (Connect_data =       (SERVER = dedicated)   (service_name = ORCL)) )

Sid_list_listener = (Sid_list = (Sid_desc = (Global_dbname = orcl) (Oracle_home =/opt/a pp/oracle/product/11.2.0/db_1) (Sid_name = orcl)))


LISTENER = (Description_list = (DESCRIPTION = (address = (PROTOCOL = IPC) (KEY = EXTPROC1521)) (address = (PROTOCOL = TCP) (HOST =     (PORT = 1521)) )   )

Sid_list_listener =

(Sid_list =

(Sid_desc =

(Sid_name = Plsextproc)

(Oracle_home = /opt/oracle/product/11g)

(program = Extproc)

(envs= "Extproc_dlls=any")


(Sid_desc =

(Global_dbname = ORCL)

(Oracle_home = /opt/oracle/product/11g)

(Sid_name = ORCL)



Sqlldr database user name/password @ Instance name Test

4 Execute command, import file

Sqlldr Userid=nrmdb/[email protected] control=/opt/informix/data/circuit.ctl



Informix Date fields are imported into Oracle and cannot be imported directly because they are not of the same type

A relay was made through a field.

For example, the Informix date field is time_stamp;

Create a TIME_STAMP1 varchar (255) Date field in Oracle to receive Informix;

Also creates a time_stamp date in Oracle for converting date fields from varchar to date in Oracle

Update circuit Set time_stamp=to_date (TIME_STAMP1, ' yyyy-mm-dd hh24:mi:ss ')

Delete the over field time_stamp1.

The type conversion is complete.


In the age of DBA scarcity, people who can put their hands on the ground are more scarce ... Do more hands-on practice, and then discuss what is feasible and persuasive

Because, only in the process of practice, can encounter a variety of problems, need to go to conquer it ~ ~ ~

Come on, ~~~trouble shooter ^_^.

How Informix data is imported into the Oracle database

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.