DESIGNATION:
- SQL * LOADER loads data from external files into tables of an oracle database.
- We do not insert the data into the table using SQL * LOADER directly.
- We need a control file for inserting the data.
- SQL * LOADER is used to just execute the commands of the control file.
- The flatfile saving process is normally we will save .txt files.
- The Control file saving process is normally we will save .ctl files.
BAD FILE:
- A bad file is used to store the rejected records.
- The rejected records will store in a bad file automatically.
- If you wrongly type the data into a flat file, you will surely get some errors. So that error during SQL * LOADER process which stores in bad file.
TYPES OF FLAT FILE:
Variable record flatfile This is with delimeter eg., 101, abc, 2000
Fixed record flatfile This is without delimeter eg., 101abc2000
The following picture illustrates the SQL * LOADER process:
SYNTAX:
$sqlldr <space> userid = username / password
control = ‘path of the controlfile’ <space> log = test.log
Creating a controlfile for variable record flatfile:
BY USING INSERT LOAD DATA PROCESS:
load data
infile ‘path of the flatfile’
insert / append / truncate / replace
into table <table_name>
fields terminated by ‘delimeter_symbol’
optionally enclosed by ‘delimeter_symbol’
trailing nullcols
(col1, col2, col3,……)
OVERALL STEPS:
To execute the SQL * LOADER tool, you need at least three files.
- The input data files store delimited data
- The parameter file stores the location of the input and output files.
- The control file contains the specification on how data is loaded.
Step 1: Create the table at the database level
View the table :
Step 2: Create the text document by using the vi editor in os level
View the text file
Step 3: Create the control file by using the vi editor in the os level
View the control file 
Step 4: Execute the SQL * LOADER syntax
Step 5: View the table
BY USING APPEND LOAD DATA PROCESS:
load data
infile ‘path of the flatfile’
append
into table <table_name>
fields terminated by ‘delimeter_symbol’
optionally enclosed by ‘delimeter_symbol’
trailing nullcols
(col1, col2, col3,……)
Load data in the control file:
View the APPEND output:
BY USING TRUNCATE LOAD DATA PROCESS:
load data
infile ‘path of the flatfile’
truncate
into table <table_name>
fields terminated by ‘delimeter_symbol’
optionally enclosed by ‘delimeter_symbol’
trailing nullcols
(col1, col2, col3,……)
Load data in the control file:
View the TRUNCATE output:
BY USING REPLACE LOAD DATA PROCESS:
load data
infile ‘path of the flatfile’
replace
into table <table_name>
fields terminated by ‘delimeter_symbol’
optionally enclosed by ‘delimeter_symbol’
trailing nullcols
(col1, col2, col3,……)
Load data in the control file:
View the REPLACE output:
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter: https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8
