SQL * Loader

It is one of the Oracle Tool will be used to transfer the data from Flat files into
Oracle Base tables.
When we are developing the Interfaces or Conversion program in Oracle Applications
then we will be using the SQL Loader Programs.

while working with SQL * Loader we will come across 5 Files.

1)Flat File OR data File:
Data file contains the data in the specific format which we will receive from the
client. this file will be generated by the legacy system(Other Systems) program.
It may be either .txt format
.dat
.csv(Comma Separated View) Excell sheet.
Most of the times we will receive in the (.csv) format

2)Control File :
—————–
Is nothing but a SQL*Loader Program will be used to load the data from file into table.
we will develop the control File
Execute the Control File.
While developing the Control file we will specify follwoing things.
1)Data File path
2)Database Table Name
3)Column Mapping.
Control file Extension is (.ctl)

3)Bad File:
———–
when we execute the COntrol file if any records are rejected by SQL *Loader then we
will find those rejecteds record in the Bad file.
SQl loader recjects the records if there are any datatype mismatch
Internal Errors
Data file Format problem
Bad File Extension is (.bad)

4)Discard File:
—————
when we execute the COntrol file if any records are rejected by Control File then we
will find those rejecteds record in the Discard file.
Control file Recjects the record when the record is not satisfying the record which
are not satisfied the condition specified in the Control file.
Discard File Extension is (.dis)

5)Log File :
————-
It contains the Log information like Program start time,End Time
No of succesfully records upload
Rejected Records into the Bad file
Rejected records into the Discard file
Any errors are there in the Control file those error message details we will find.
Log file Extension is (.log)

Control File Syntax:
———————-
LOAD DATA
INFILE ‘Data File Path’
Insert into table
FIELDS TERMINATED by ‘,’ OPTIONALLY ENCLOSED BY’”‘
TRAILING NULLCOLS
(Column1 ,
column2 ,
column3 ,……)

Execution of Control File :
—————————–
Goto Command Prompt where the SQl loader is Installed.
D:Oracle\Proddb\8.1.7\bin\ SQLLDR Username/Password@PROD
Control = Control file Path.

============================================
1)Connect to Scott schema create temp table
2)Prepare the data file having some sample records save in the local mechine.
3)Develop the control file like follows.

load data
infile ‘C:\22\LOAD\EMP.txt’
INSERT INTO TABLE EMP_DETAILS22
fields terminated by ‘,’
(empno ,
ename ,
sal ,
job ,
comm ,
tax ,
deptno ,
jdate)

Note: Save the file extension as “.ctl”.

4)Goto Command Propmt through Start Menu = Run = > CMD
5)Connect to following path and execute the control file
D:\Oracle\Proddb\8.1.7\Bin\SQLLDR Scott/Tiger@PROD
Control = C:\22\LOAD\emp_details.ctl
6)test the data in the Table.