Loading data in an oracle table from a file using sqlldr

We will see how to load data from a file in an oracle table using sqlldr.

Table

We have a table EMPLOYEE in the oracle database with the following schema:-

SQL> desc EMPLOYEE;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMP_ID                                    NOT NULL NUMBER(38)
 EMP_NAME                                           VARCHAR2(500)
 EMP_AGE                                            NUMBER(38)

Data File

We have a data file ‘employees.csv‘. We will load data from this file into EMPLOYEE table. the contents of the file are :-

1,rajesh,29
2,rahul,24
3,mahesh,31
4,suresh,22

CONTROL file

Control file describes how to load data in the table. We will create a control file ‘loaddata.ctl‘ with the following content:-

LOAD DATA INFILE 'employees.csv' INTO TABLE employee APPEND FIELDS TERMINATED BY ',' (
EMP_ID,
EMP_NAME,
EMP_AGE
)

Provide the absolute/relative path of the data file to load the data from. In our case the file is ‘employees.csv‘. Provide column names in the exact sequence as present in the data file.

sqlldr

On the command prompt run the following command to run the sqlldr utility to load the data:-

sqlldr userid=username/password control=loaddata.ctl log=loaddata.log

control – specifies the name of the SQL Loader control file that describes how to load data. In our case the file is ‘loaddata.ctl
log – specifies the log file that SQL Loader will create to store logging information about the loading process. The log file contains a detailed summary of the load, including a description of any errors that occurred during the load.
bad – specifies the name of the bad file created by SQL Loader to store records that cause errors during insert or that are improperly formatted. Default name – the name of the datafile, with an extension of .bad.

 

Leave a Reply

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