SQL*Loader :

 SQL*Loader


SQL*Loader :
This is a CUI (Character User Interface) Tool, which is used to load data from flat files to tables. We have to feed control file to the SQL Loader this control file contains information about the flat file and into which table needs to be loaded.

1) DATA FILE :
This is file which actual data to be loaded.

2) CONTROL FILE:
This is key program to load the data.It provides information to SQL*Loader.In control file we specify name and location of the input data file, format of the records, name of the table or tables to be loaded,condition for selecting the data,name and location of bad and discard file.

3) BAD FILE :
This file contains the records which are rejected by SQL*Loader.Whenever SQL*Loader encounter a database error while to trying to load a record , it writes that record to a file known as BAD FILE. Extension of bad file (.bad)

4) DISCARD FILE:
This file will be created automatically when we executed control file. It contains the records which are rejected by control file, control file will reject the records if any of the condition was not satisfied mentioned in the file.

5) Log File:
This file contains the record of Loader activites during a load session.It contains names of control file, log file, bad file, discard file and data file, error messages for records that cause errors , sucessful record count.The extension of Log file is (.log)

CONTROL FILE SYNTAX:

LOAD DATA
INFILE ‘DATA FILE PATH’
INSERT INTO TABLE
FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘ “ ‘ TRAILING NULL COLS
(COLUMN..1,COLUMN..2,COLUMN..3,COLUMN..4………………………………….COLUMN..N)
TO EXECUTE CONTROL FILE:

SQLLDR CONTROL_FILE PATH USERNAME/PASSWORD @DATABASE.




EXAMPLE:
Step 1: CREATE DATA FILE IN THE EXTENSION OF ( .txt), ( .dat),( .csv)
DATA FILE……….. E:\RAJ\EMP.txt (CREATE THIS FILE IN NOTEPAD)
1,RAJKUMAR,20,22-MAY-2002
2,SIKINDAR,20,12-FEB-2004
3,VENU,30,11-MAR-2001
5,RAMU,40,15-DEC-2001
6,ANU,20,15-JAN-2000
7,ROJA RAMANI,50,18-FEB-1999

step2: connect to any of the user and create the table based on the given data.

Connect scott/tiger@prod
CREATE TABLE XX_EMP ( EMPNO NUMBER(5),
ENAME VARCHAR2(20),
DEPTNO NUMBER(3),
JOINDATA DATE)

step 3: Create control file ( .CTL PROGRAM )
CONTROL FILE…………E:\RAJ\ EMP.ctl
LOAD DATA
INFILE ‘E:\RAJ\APPS\EMP.TXT’
INSERT INTO TABLE XX_EMP
FIELDS TERMINATED BY ‘,’
(EMPNO,ENAME,DEPTNO,JOINDATE)