Loading large data into Oracle database using SQL* Loader

SQL * Loader is very useful when loading large data, which are very tedious and time consuming task, to the database. Suppose you have a 10000 rows in .csv file and you have to load all the rows to the database. Now what would you do? Would you enter each and every row individually and spends years to do it? obviously not. For this task there is a very useful tool SQL *Loader which load large data file into database in very short time. Here I will give an small example of loading the data which are in .csv file to the ORACLE database located in remote host (same steps can be used to load into local machine).

Suppose I have table TIME_MTH_DM in my database which has following attributes

• MTH_IDNT
• DM_RECD_LOAD_DT
• QTR_IDNT
• YR_IDNT
• MTH_DESC
• HALF_IDNT
• MTH_START_DT
• MTH_END_DT

And I also have data file in .csv file containing about 5000 records as follows

MTH_IDNT,QTR_IDNT,YR_IDNT,MTH_DESC,HALF_IDNT,MTH_START_DT,MTH_END_DT
200001,200001,2000,January,200001,2000-01-01,2000-01-31
200001,200001,2000,January,200001,2000-01-01,2000-01-31
200001,200001,2000,January,200001,2000-01-01,2000-01-31
200001,200001,2000,January,200001,2000-01-01,2000-01-31
200001,200001,2000,January,200001,2000-01-01,2000-01-31
200001,200001,2000,January,200001,2000-01-01,2000-01-31
200001,200001,2000,January,200001,2000-01-01,2000-01-31
and so on upto 5000 rows

To accomplish the job I will make two files one control file and other script file. The content of control file load-items.ctl is as follows

OPTIONS (skip=1, errors=100, rows=5000, direct=True)
LOAD DATA
INFILE ‘load-items.csv’
TRUNCATE into table TIME_MTH_DM
FIELDS TERMINATED BY ‘,’ optionally enclosed by ‘”‘
(
        MTH_IDNT,
        QTR_IDNT,
        YR_IDNT,
        MTH_DESC,
        HALF_IDNT,
        MTH_START_DT date “YYYY-MM-DD”,
        MTH_END_DT date “YYYY-MM-DD”
)

skip = > skip the first row of the file load-items.csv
errors => no of errors allowed
rows => no of rows to load

And the content of script file loadScript.sh written in Bash shell is as follows

!# /bin/sh

sqlldr USERNAME/PASSWORD@HOST:1521/SID control=load-items.ctl log=load-items.log bad=load-items.bad discard=load-items.discard

After running this file the sql loader loads the data in .csv file to corresponding columns of database table. For example data in column MTH_IDNT in load-items.csv file is loaded to MTH_IDNT column of database table TIME_MTH_DM and so on.

In process of loading if any data rows are discarded, then those data moves to file load-items.discard. Similarly errors are stored in load-items.log. By seeing the load-items.log you can track the errors. 

Just change the permission of file script file to executable

chmod +x loadScript.sh

and run the file using

./loadScript.sh

There are many chances of errors, some are listed below

1.  If the fields in .csv file do not match with fields in Database table
2.  If the data type of the corresponding fields do not match
3.  If the length of the fields in .csv file are larger than Database table
4.  If the date format is invalid. Oracle support date format in DDMMYYYY example 10feb2011. To make the date field compatible with Oracle date field enter date “YYYY-MM-DD” as shown above in .ctl file

and so on