SQL*Loader
This is one of the oracle tool will be used to transfer data from flat file to oracle database table.
1) Data file :
• It contains data in specific format.
• The extension of datafile will be
1. .txt files
2. .dat files
3. .csv files (comma separated value)
2) Control file:
• This is mail sql * loader program
• This is used to transfer data from file to database table.
• We will write datafile path and database table name and column mapping sothat data will be transferred into database columns.
• Extension of this file is .ctl
3) Bad file:
• It contains the records which are rejected by sql * loader
• Sql * loader will reject the records if record format is not compatable with database table column.
• If any internal error occurs that time also record will be inserted into bad file.
• Extension of this file is .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 record is not satisfying the condition which we have mentioned in control file.
• The exetension of this file is .dis
5) Log file:
• It contains the records ,records status like
1. Successful record count
2. Badfile count
3. Discard file
4. If any errors are there those error messages will be displayed in the log file.
• The extension 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:\ramesh\apps\emp.txt (create this file in notepad)
12,ramesh,10,10-jan-2000
13,mahesh,15,12-mar-2005
15,anil,20,15-jun-2005
14,rajkumar,20,22-may-2002
20,sikindar,20,12-feb-2004
23,venu,30,11-mar-2001
25,ramu,40,15-dec-2001
22,anu,20,15-jan-2000
27,roja ramani,50,18-feb-1999
Step 2: connect to the any of the user and create the table based on the given data.
connect scott/tiger@prod
sql> create table xx_emp ( empno number(5),
ename varchar2(20),
deptno number(3),
joindate date)
Step 3: create sql loader program ( .ctl program )
control file…………e:\ramesh\apps\emp.ctl (create this file in notepad)
load data
infile ‘e:\ramesh\apps\emp.txt’
insert into table xx_emp
fields terminated by ‘,’
(empno,ename,deptno,joindate)
Step 4: execute the control file, follow the following steps :
Sql >host (press enter )
It opens the window in dos mode
It shows the path where sqlldr file available.
Using this sqlldr only u can execute .ctl file
Generally it is available in the following path
D:\oracle\proddb\8.1.7\bin>
Type the following command at the prompt
D:\oracle\proddb\8.1.7\bin>sqlldr scott/tiger@prod
It will ask the path of the control file
Control= e:\ramesh\apps\
It will insert all the data present in e:\ramesh\apps\emp.txt to the xx_emp table which is present in scott user it will display the result in the database
Sql> select * from xx_emp;
empno ename deptno joindate
---------- -------------------- ---------- ---------
12 ramesh 10 10-jan-00
13 mahesh 15 12-mar-05
15 anil 20 15-jun-05
14 rajkumar 20 22-may-02
20 sikindar 20 12-feb-04
23 venu 30 11-mar-01
25 ramu 40 15-dec-01
22 anu 20 15-jan-00
27 roja ramani 50 18-feb-99
9 rows selected.
Note: if you want to see the discard file information, we can specify discard file explicitly by using discard file reserved word (.dis file)
Load data
Infile ‘e:\ramesh\apps\filename.ctl’
Discardfile ‘e:\ramesh\apps\filename.dis’
………..
……………….
……………….
……………………….
By using 2 parameters u can restrict the data when uploading from flat file to table
The parameters are Skip and load
Skip: is used to skip first n number of rows.
Load: is used to load n number of rows.
Example:
>sqlldr username/ password@database name skip=5
Here u r skipping first 5 rows. And inserting remaining rows.
>sqlldr username/password@databsename load=6
Here u r loading first 6 records only into the table.
>sqlldr username/password@database name skip=2 load=4
Here u r skipping first 2 rows and loading next 4 rows.
In sqlldr, between command is not there. By using skip and load commands u can overcome that problem.
Example:
There are 25 record in flat file.
If u want to load the records from 12 to 18
The syntax is:
>sqlldr username/password@databasename skip=11 load=7
It will skips first 11 rows and loads next 7 rows.
Where clause in control file (.ctl file)
By using when command, we can implemented where conditions.
Example:
load data
infile ‘e:\ramesh\apps\emp.ctl’
discardfile ‘e:\ramesh\apps\emp.dis’
when deptno=’10’
(empno,ename,deptno,joindate)
After run this program it will insert only deptno=10 records.
Sql>select * from xx_emp;
Empno ename deptno joindate
------ --------------- ---------- ------------
12 ramesh 10 10-jan-00
Note : it will upload only one record. Because with deptno=10 only one record available in the data file.
Data file is in excel sheet (. Csv format)
Step 1: create .csv file in excel sheet and save as filename.csv
Step 2: develop the table in any user based on .csv file
Step 3: develop the control file (.ctl)
Step 4: run the control file.
Program as follows:
Load date
Infile ‘e:\ramesh\apps\emp_excel.csv’
Insert into table xx_emp when deptno=10
Terminated by ‘,’
(empno,ename,deptno,joindate)
To insert null values to a particular column
Load date
Infile ‘e:\ramesh\apps\emp_excel.csv’
Insert into table xx_emp when deptno=10
Terminated by ‘,’
(empno,ename,deptno,joindate filler)
Using the filler keyword u can insert null values to a particular column
Using sql functions in control file
Load data
Infile 'e:\ramesh\apps\emp.txt'
Truncate into table xx_emp
Fields terminated by ','
(empno ":empno+10",
ename "upper(:ename)",
deptno,
joindate filler)
Using sequences in control file.
Load date
Infile ‘e:\ramesh\apps\emp_excel.csv’
Insert into table xx_emp when deptno=10
Terminated by ‘,’
(empno (sequencename.nextval),
ename lower(:ename),
deptno,
joindate filler)
Database fixed format
Empno : 5 digits
Ename : 10 characters
Dept no: 2 digits
Join date: 11 characters.
The file is look like
123 ramesh 2012-jan-2004
124 mahesh----2002-feb-2005
144—rajesh----2015-dec-2006
Save as emp1.txt
Control file (emp1.ctl)
Load data
Infile ‘e:\ramesh\apps\emp1.txt’
Insert into table xx_emp
(empno position(1:5),
ename position(6:15),
deptno position(16:17),
joindate position(18:28))
Sql> select * from xx_emp;
empno ename deptno joindate
---------- -------------------- ---------- -----------
123 ramesh 20 12-jan-04
124 mahesh 30 02-feb-05
144 rajesh 10 15-dec-06
Data in two files:
The file is look like
123--ramesh----2012-jan-2004
124—mahesh----2002-feb-2005
125—rajesh----2015-dec-2006
126—anil------2316-dec-2005
127—nanda-----5012-feb-2006
Save as e:\ramesh\apps\emp1.txt
128—anu------2511-mar-2000
129—roja------3031-jan-2002
130—rajkumar—2929-jan-2006
131—srinivas—4012-may-2001
Save as e:\ramesh\apps\emp2.txt
Control file (emp1.ctl)
Load data
Infile ‘e:\ramesh\apps\emp1.txt’
Infile ‘e:\ramesh\apps\emp2.txt’
Insert into table xx_emp
(empno position(1:5),
ename position(6:15),
deptno position(16:17),
joindate position(18:28))
The output looks like, Sql> select * from xx_emp;
empno ename deptno joindate
---------- ------------------ ---------- ---------
128 anu 25 11-mar-00
129 roja 30 31-jan-02
130 rajkumar 29 29-jan-06
131 srinivas 40 12-may-01
123 ramesh 20 12-jan-04
124 mahesh 20 02-feb-05
125 rajesh 20 15-dec-06
126 anil 23 16-dec-05
127 nanda 50 12-feb-06
Different tables and single datafile:
There are two tables:
1) xx_emp
2) xx_dept
Xx_emp table xx_dept table
------------------ ------------------
Empno: 5numbers deptno: 2 numbers
Ename: 10 characters dname: 15 characters
Deptno: 2 numbers
Join date: 11 characters
The data is in two files
123 ramesh 2012-jan-200420finance
124 mahesh 2002-feb-200520sales
125 rajesh 2015-dec-200620marketing
126 anil 2316-dec-200523staff
127 nanda 5012-feb-200650production
Save this file as e:\ramesh\apps\emp1.txt
128 anu 2511-mar-200025finance
129 roja 3031-jan-200230production
130 rajkumar 2929-jan-200629markating
131 srinivas 4012-may-200140staff
Save this file as e:\ramesh\apps\emp2.txt
The control file is:
Load data
Infile 'e:\ramesh\apps\emp1.txt'
Infile 'e:\ramesh\apps\emp2.txt'
Insert into table xx_emp
when empno!=' '
(empno position(1:5),
ename position(6:15),
deptno position(16:17),
joindate position(18:28))
Into table xx_dept
when deptno !=' '
(deptno position(29:30),
dname position(31:45))
The output look like as:
Datafile is present in the control file itself:
The control file is
Load data
Infile *
Insert into table xx_emp
Fields terminated by ','
(empno ,
ename,
deptno,
joindate)
Begindata
12,ramesh,10,10-jan-2000
13,mahesh,15,12-mar-2005
15,anil,20,15-jun-2005
20,sikindar,20,12-feb-2004
23,venu,30,11-mar-2001
25,ramu,40,15-dec-2001
22,anu,20,15-jan-2000
27,roja,50,18-feb-1999
The output is look like:
Sql> select * from xx_emp;
empno ename deptno joindate
---------- --------- ---------- ---------
12 ramesh 10 10-jan-00
13 mahesh 15 12-mar-05
15 anil 20 15-jun-05
20 sikindar 20 12-feb-04
23 venu 30 11-mar-01
25 ramu 40 15-dec-01
22 anu 20 15-jan-00
27 roja 50 18-feb-99
8 rows selected.
SQL*Loader Based Concurrent Program registration steps:
Step 1: Develop the control file as per requirement.
Step 2: Move the control file into the server in the specified path.
Step 3: Create executable with execution method as SQL * loader.
Step 4: Create concurrent program and attach executable, parameter, incompatibilities.
Step 5: Create request group and attach concurrent program.
Step 6: Create responsibility and attach request group.
Step 7: Attach responsibility to the user.
Step 8: User will submit request from SRS window.
No comments:
Post a Comment