SQL Loader Utility for Oracle Database

What is SQL Loader

SQL*Loader is a utility to load data from text file to Oracle data base. SQL Loader reads data from plain text file and apply instructions from control file and load data in specified table or tables.

How to use SQL Loader

Sample SQL*Loader control file

OPTIONS (SKIP=1)
LOAD DATA APPEND
INTO TABLE xxap_invoice_interface
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
invoice_number,
vendor_name,
vendor_site_code,
invoice_amount,
invoice_date DATE "DD-MON-YYYY",
invoice_description   ,
line_number           ,
line_amount           ,
process_flag    CONSTANT "N"
)

Sample Data file

INVOICE_NUMBER,VENDOR_NAME,VENDOR_SITE_CODE,INVOICE_AMOUNT,INVOICE_DATE,DESCRIPTION,LINE_NUMBER,LINE_AMOUNT
TEST7311,"CDS, Inc",PITTSBURGH,200,25-NOV-2016,This Invoice is created for test,1,100
TEST7311,"CDS, Inc",PITTSBURGH,200,25-NOV-2016,This Invoice is created for test,2,100
TEST7312,"CDS, Inc",PITTSBURGH,200,25-NOV-2016,This Invoice is created for test,1,100
TEST7312,,,200,25-NOV-2016,This Invoice is created for test,2,100
TEST7313,"CDS, Inc",,100,25-NOV-0016,This Invoice is created for test,1,100

Explanation of Control File

Line1
Skipping one row from the data file usually this is column header, which need not to be loaded in table.

Line 2
LOAD Data append will add the data in the table, other option is replaced which will delete the data and load the new data. 

Line3

INTO TABLE XXAP_INVOICE_INTERFACE will specify the table, where data needs to be loaded. 

Line 4
Line 4 specified that all the data fields are terminated by comma and enclosed in double quotes. It is a good practice to include data in double quotes, it will load data in same column even if data consist comma ',' which is a field separator. 

Line 5 onwards
After this it have list of data fields, which are loaded in the table sequentially. 

Considerations
Date format should be applied because often this does not match with the data base session default date format, it will help in loading dates without any errors. 
Constants can be specified in control file and need not to be a part of data file. 

SQL*Loader command assuming that both control file and data file in the same directory where the command is being issued otherwise fully qualified name is required in sqlldr command parameter.

sqlldr userid=<user id>/<password> control=invoice_load.ctl data=data_file.csv

More information on oracle

More on SQL Loader