Using SQL LOADER in Oracle to import CSV file

Loading data using SQL Loader


Scenario: Suppose we have some legacy system(Main Frame) and now we want to transfer data into new system (Oracle).

Export data from legacy system(Here I am using .csv file format)


Now Create Staging table in Oracle database:


CREATE TABLE "APPS"."XX_TEMP_EMP"

( "PERSON_ID" VARCHAR2(20 BYTE) NOT NULL ENABLE,

"EFFECTIVE_START_DATE" DATE NOT NULL ENABLE,

"EFFECTIVE_END_DATE" DATE NOT NULL ENABLE,

"DATE_OF_BIRTH" DATE,

"EMAIL_ADDRESS" VARCHAR2(20 BYTE),

"EMPLOYEE_NUMBER" NUMBER NOT NULL ENABLE,

"FIRST_NAME" VARCHAR2(20 BYTE),

"MIDDLE_NAME" VARCHAR2(20 BYTE),

"SEX" VARCHAR2(20 BYTE),

CONSTRAINT "XX_TEMP_EMP_PK" PRIMARY KEY ("PERSON_ID")

)

Creating Controller file to load data into oracle db


OPTIONS (SKIP = 1)

LOAD DATA

INFILE '/testapp/oracle/R12TEST/fs1/EBSapps/appl/xxola/12.0.0/bin/Employee.csv'

insert INTO TABLE XX_TEMP_EMP

FIELDS TERMINATED BY ','

OPTIONALLY ENCLOSED BY '"'

TRAILING NULLCOLS

(

PERSON_ID,
EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE,
DATE_OF_BIRTH,
EMAIL_ADDRESS,
EMPLOYEE_NUMBER,
FIRST_NAME,
MIDDLE_NAME,
SEX
)

Put CONTROLER_EMP.ctl and Employee.csv in server.


Create Executable in EBS Application Developer


Create concurrent program


Defining Parameter


Assign Responsibility for your Program.


Adding program


Now You can Run the Program from System Administrator Responsibility


See the Log


Now see the loaded data in Oracle Database


 

Leave a Reply

Your email address will not be published. Required fields are marked *