Place the CSV or any delimited file in the Oracle server (Ex: stud_course.csv). For the sake of example, assume this file has 3 columns i.e. ID, Name, and course.
The table must be created in Oracle already with column names equal to the text file. As we are loading the data into the existing table, the table must be presented.
create table scott.stud_course(
Create a control file (i.e. configuration file) with the below content.
into table scott.stud_course
fields terminated by ‘,’ trailing nullcols
Name this file as ‘student_course.ctl’ and save.
Run below command from the CMD level in windows or linux terminal (if sa is linux)
sqlldr utility is available in the respective installation location, for example, we can see the utility in C:\app\OraUser\product\12.1.0\dbhome_1\BIN folder if Oracle is installed in Windows.
sqlldr ORACLE_USER/Welcome1234@orcl control=student_course.ctl log=student_course.log direct=true
ORACLE_USER – database user name
Welcome1234 – user’s password
control=student_course.ctl – Control file’s name which we created above.
log=student_course.log – Log file to store the log messages.
direct=true – use direct method instead of the conventional way – This is much faster
Once we run the above command, below content will be updated in the log file and data will be loaded into the Oracle table.
Column array rows: 5000
Stream buffer bytes: 256000
Read buffer bytes: 1048576
Total logical records skipped: 0
Total logical records read: 10510500
Total logical records rejected: 0
Total logical records discarded: 0
Total stream buffers loaded by SQL*Loader main thread: 2479
Total stream buffers loaded by SQL*Loader load thread: 1983
Run began on Thu Jun 16 16:49:35 2019
Run ended on Thu Jun 16 16:50:01 2019
In our test run, 10 million records (~500 MB) loaded in ~ 30 seconds. Above statistics have been taken from our test run.