SQL* Loader
SQL* Loader is a tool in Oracle. It is used to transfer data from flat file to
Oracle database tables.
There are 5 types of files in SQL *
Loader given below.
1)
Flat File: - It is also called as data File. It
contains the data in specific format. This format may be fixed length or ‘,’
separated or variable format. Extensions of the flat files are ‘.txt’,’.dat’,’.csv’
(comma separated view) or Excel sheet and .sql.
2)
Control File: - It is the SQL * Loader file or program. It
is used to Load the data from Flat file to table. It contains flat file path
and table name and column mappings. Extension of control file is ‘.ctl’.
Syntax of Control
file:
OPTIONS(SKIP=1)
LOAD DATA
infile '*'
REPLACE/INSERT/APPEND into
table table_name
fields terminated by ','
optionally enclosed by
'"'
trailing nullcols
(
Column1,
Column2,
Column3,
Column n
"REPLACE(:Column n,CHR(13),'')"
)
3)
Bad File: - whenever we execute the control file, if
SQL * Loader rejects the records then the bad file will be created
automatically. Extension of bad file is ‘.bad’. Bad file will have the
rejected records which are rejected by SQL * Loader. SQL * Loader will rejects
the records if data is not incorrect format and if any internal error occurs.
4)
Discard File: - Discard file contains the records, which are rejected
by control file. Control file will rejects the records if we write any
conditions, system will check each record based on whether it is satisfies the
condition or not . If record is not satisfying the condition it will come into
the discard file. Extension for discard file is ‘.dis’.
5)
Log File: - Log File will be created automatically after completion
of control file execution process. It contains the information like count of successful
records and bad file discard file and so on. Extension of log file is ‘.log’.
SQL * Loader Methods: - SQL *Loader methods are 3 types. They were shown
below
INSERT: - In INSERT mode system will insert the data
into the table, but table should be empty.
REPLACE: - In REPLACE mode system will delete or remove
the existing data and inserts the new data into the table.
APPEND: - In APPEND mode system will add the
new data for the existing data.
SQL * Loader Command:- After completing the control file we will execute the
control file by using following command in putty.
1.
Login to command
prompt where the SQL *Loader was installed (Putty).
After
setting the environment variable (.env file)
2. sqlldr
userid/password@database name press ENTER then
control =
<control filename>
ex:-
c:\orant\bin> sql ldr apps/apps@prod
control = emp.ctl
- Before going to work with SQL * Loader we should have to
verify two things
- Data file
- Table
- Develop the control file as per the flat file and table
format.
- Go to the command prompt execute the control file by
using following syntax.
Example:-
Create table XXST_VENDOR_SITE_UPDATE
(VENDOR_ID NUMBER,
VENDOR_NAME VARCHAR2 (4000),
SEGMENT1
VARCHAR2 (4000),
VENDOR_SITE_ID
NUMBER,
VENDOR_SITE_CODE
VARCHAR2 (4000),
TERMS_DATE_BASIS
VARCHAR2 (4000),
TERMS_NAME VARCHAR2 (4000)
);
Data file:-
|
VENDOR_ID
|
VENDOR_NAME
|
SEGMENT1
|
VENDOR_SITE_ID
|
VENDOR_SITE_CODE
|
TERMS_DATE_BASIS
|
Terms Name
|
|
2
|
Service Tax Authority(India)
|
100002
|
1
|
INDIA
|
Invoice Received
|
30 NET
|
|
3
|
VAT Authorities(India)
|
100003
|
4
|
INDIA
|
Goods Received
|
60 NET
|
|
4
|
TDS Authorities (India)
|
100004
|
41006
|
192B_INDV
|
Current
|
IMMEDIATE
|
|
6
|
Synechron Limited (UK)
|
100006
|
67
|
ST_UK
|
Goods Received
|
5 NET
|
|
2011
|
HDFC Bank Credit Card No. 4050 2810 0036 4759 ( Parag Deshpande)
|
101019
|
1985
|
IND_SITE
|
Invoice Received
|
10 NET
|
|
2012
|
Hammer Publishers Private Limited
|
101020
|
1986
|
IND_SITE
|
Current
|
20 NET
|
|
2013
|
Hindustan Advertising Agencies
|
101021
|
1987
|
IND_MH_SITE
|
Current
|
15 NET
|
|
2014
|
Hinjewadi Industries Association
|
101022
|
1988
|
IND_SITE
|
Goods Received
|
45 NET
|
Control file:-
OPTIONS(SKIP=1)
LOAD DATA
infile '*'
REPLACE into table
XXST_VENDOR_SITE_UPDATE
fields terminated by ','
optionally enclosed by
'"'
trailing nullcols
(
VENDOR_ID ,
VENDOR_NAME ,
SEGMENT1 ,
VENDOR_SITE_ID ,
VENDOR_SITE_CODE ,
TERMS_DATE_BASIS,
TERMS_NAME
"REPLACE(:TERMS_NAME,CHR(13),'')"
)
open the command
prompt
sqlldr
apps/apps@databasename control='$AP_TOP/bin/vendor_site_update.ctl'
data='$AP_TOP/bin/vendor_site_update.csv'
log='$AP_TOP/bin/vendor_site_update.log'
Example1:-
Create table csv_emp (empno number(3), ename varchar2(100), joindate date,
deptno number(3));
Control file:-
Load data
infile ‘c:/sloader\emp_details.csv’
discard file ‘c:\sloader\emp_details.dis’
Insert into table csv_emp
Where deptno=’10’
Fields terminated by ‘,’
(startdate,enddate,deptno,dname,project)
Trialing NULL Columns:- We can use this syntax into the null
columns to insert null values if data file is not having data.
Syntax:-
Load data
infile ‘c:/sloader\emp_details.csv’
discard file ‘c:\sloader\emp_details.dis’
Insert into table csv_emp
Where deptno=’10’
Fields terminated by ‘,’
Trailing null cols
(empno “sempno.nextval”,
ename “init cap(:ename)”,
Deptno,
Joindate sysdate)
Whenever the client
gave data file in fixed format like (data is not separated by commas(‘,’))
tempo is first 5 digits, ename is 10 digits and deptno is 2 digits. Then the
control file is looks like this
Load data
Infile ‘c:\sloader\fixed_emp.dat’
Insert into table fixed_emp
(empno position (1:5);
ename position (6:15);
deptno position(16:17))
Inserting Data Into
Multiple Tables:-
load data
infile ‘c:\sloader\fixed.dat’
insert into table fixed_emp
when empno = ‘’
(emp no position (1:5),
ename position(6:15),
dept no (16:17))
into table fixed_dept
where dname = ‘’
(dname position (18:25).,
project position (26:28),
startdate position(29 : 39)
Inserting Data into
Single Table from Multiple Files:-
LOAD DATA
infile ‘c:\first.dat’
infile ‘c:\second.dat’
insert into table fixed_emp
fieldes terminated by ‘,’
(empno, ename, deptno)
Without data file
creating the control file:-
Load data
Infile *
Insert into table fixed_emp
Fieldes terminated by ‘,’
(empno, ename, deptno)
BEGIN DATA
1014,operations,10
1234,Scott,10
456,urman,20
789,sysadmin,10
FILLER: -
If you want to ignore complete data in a column we can used the ‘FILLER’. It is
reserved word for SQL * Loader. System will not consider the particular column.
SQL * Loader program registration in Oracle Apps:-
1. First we will develop the
SQL* Loader file as per data file and table format.
2. Then move this control file
into the server.
3. Connect to System
Administrator Responsibility then create executable and execution method as SQL
* Loader and execution file name as control file name without extinction.
4. Create Concurrent Program and
attach executable parameters and incompatibilities.
5. Create Request Group and attach
Concurrent Program.
6. Create Responsibility and
attach Request Group to responsibility.
7. Create User and attach
Responsibility to User.
8. User will submit the Request
from SRS Window.
SQL * Script Registration into Oracle Apps:-
1. First we will develop the
SQL* Script as per client requirement which has got SQL* Plus commands.
2. Then we will move this ‘.sql’
file into the server.
3. Create Executable with
execution method as SQL* Plus and we will give the SQL filename.
4. Create Concurrent Program and
attach executable parameters and incompatibilities.
5. Create Request Group attaches
Concurrent Program.
6. Create Responsibility and attach
Request Group to Responsibility.
7. Create User and attach
Responsibility to User.
8. User will submit the Request
from SRS Window.
Develop the SQL*
script save it as .sql file move this file into sql folder and
create executable
