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.