Step 4: Write SQL Loader control file
We have to write a SQL Loader control file describing the input file, loading options, column structure and other settings.
The SQL*Loader control file is a text file that contains data definition language (DDL) instructions. DDL is used to control the following aspects of a SQL*Loader session:
- Where SQL*Loader will find the data to load
- How SQL*Loader expects that data to be formatted
- How SQL*Loader will be configured (memory management, rejecting records, interrupted load handling, and so on) as it loads the data
- How SQL*Loader will manipulate the data being loaded
For more detailed information please visit SQL Loader Control file reference
Open Visual Editor in Linux command prompt by typing vi and type the following script
LOAD DATA
INFILE
'/opt/oracle/customers.txt'
BADFILE '/opt/oracle/customers.bad'
DISCARDFILE '/opt/oracle/customers.dsc'
APPEND INTO TABLE
SCOTT.customers
FIELDS TERMINATED BY '\t' TRAILING NULLCOLS
(CustomerID,
CompanyName, ContactName,
ContactTitle, Address,
City, Region, PostalCode,
Country, Phone, Fax)
Here is the explanation of different commands In the above script:
- The LOAD DATA statement tells SQL*Loader that this is the beginning of a new data load.
- The INFILE clause specifies the name of a datafile containing data that you want to load.
- The BADFILE parameter specifies the name of a file into which rejected records are placed.
- The DISCARDFILE parameter specifies the name of a file into which discarded records are placed.
- The APPEND parameter is one of the options you can use when
loading data into a table that is not empty.
After writing the above contents save the file and quit vi by pressing ESC and then :wq
Next, run SQL Loader command