SQL Loader

Its nothing but " Bulk Loader Utility ".
With this concept we can load the data to the table in bulk.
Main word is LOAD.
Then comes to your mind , what is the difference between load and insert ? Insert happens one by one.Load happens in one go.
what data ? which table ? loading script ? Execute --> these are the four things YOU NEED TO KEEP IN MIND.

Flat files --> csv ( comma separated value ) , txt , dat , excel , etc.
Always use notepad to load the data.

select employee_id || ',' || first_name || ',' || salary from employees_table where rownum <= 10 ; --> this will fetch only 10 rows.

save this file in a folder as csv.

select employee_id || ',' || first_name || ',' || salary from employees_table where employee_id between 150 and 170 ; --> this will fetch rows between those values.

Save this file as txt.

Now coming to table creation

create table sample(id number , name varchar2(25) , salary number);

Now coming to creation of script

loading script or control file both are same.

load data infile 'path_of_the_file.csv' infile 'path_of_the_file.txt' insert into table sample fields terminated by ',' (id,name,salary)

create the script and save as ALL FILES ( notepad ) with .ctl file.

Now coming to Execute

sqlldr hr_schema_name/password control='file_location_of_control_file_or_execution_file' direct = true

here why direct=true --> it will load very fast and it will by-pass all constraints and triggers.
if direct=false --> constraints and triggers it will check and then it execute.

In short ,

Excluding one column

If you some column should not be loaded , then use FILLER.

load data infile 'path_of_the_file.csv' infile 'path_of_the_file.txt' insert into table sample fields terminated by ',' (id,name,salary filler)

load data infile 'path_of_the_file.csv' infile 'path_of_the_file.txt' insert into table sample fields terminated by ',' (id,name filler,salary)

In above example , salary and name will be empty . It won't load the data.

Condition

WHEN --> loading data should obey the condition which you give. If the condition fails , then it stores the failed data in DISCARD FILE.
If there is Oracle error , then it gets captured in BAD FILE.

WHEN condition should be used here,

load data infile 'path_of_the_file.csv' infile 'path_of_the_file.txt' insert into table sample when ? fields terminated by ',' (id,name filler,salary)

How to get the process summary ?

It will be stored in log file.
you can set all the files in the command itself , like below.

sqlldr hr_schema_name/password control='file_location_of_control_file_or_execution_file' log = summary.log bad = sample.bad discard = sample.dsc direct = true

If you are giving any file name here , then it will generate automatically.
So Import take here is ,

skip

If you want to skip the rows while loading , then you can specify in the command itself.

sqlldr hr_schema_name/password control='file_location_of_control_file_or_execution_file' skip = 2 direct = true

2 rows will be skipped.

Notes

SQL loader short key word is sqlldr.
insert into table sample --> this will work only when the table is EMPTY. If you try to execute again , then it throw below error.