Oracle SQL *Loader is
one of the powerful utility tools that is used to transfer data from a flat
file to an Oracle DB.
Some data is present in the local C:\<name_flatfile>
D:\
Based on the type of flat file, we are going to create a
Control File and submit to the SQL Loader Tool, then it will transfer the flat file
to the Oracle DB.
What is a Flat file?
A flat file (usually .csv, .txt, or .dat) containing the raw
information you wish to upload. There are two types of flat files available.
Variable Record Flat File: It has delimiters
Ex:
101,supplier1,hyd
102,supplier2,blr
103,supplier3,chn
In the above delimiter is ,
Delimiters like $, #...etc.
For the variable Record Flat file, we will create a separate
control file
Fixed Record Flat File: It does not have delimiters.
101supplier1hyd
102supplier2blr
103supplier3chn
After we submit the control file to the SQL LDR utility
tool, it will start the process to move the data from the flat file to the
Oracle DB. During the process, a log file will be created.
Log file extension is .log
Log file stores: errors during the process. What are errors
or messages?
How many records are rejected or accepted?
It
is just a running commentary for the process.
So, log file stores loaded/rejected/skipped records and all
other information.
Some records may not be processed to be inserted into the DB
table means rejected. These are stored in the Bad File(.bad) and the Discard
File (.dsc)
What is control file?
It will be heart of the process. It will start the engine to
run.
What is log file?
During the process, it will run the commentary and summarize
the flow.
What is a bad file?
If there is a problem in the flat file or the Oracle DB(data
type issues, Business rule violations)
Records rejected based upon “when clause condition fails” in
the control file.
In the path where the location of the flat file is
available, C:\
Go to that path, type
the command below
C:\> sqlldr userid=username/password(SYSTEM/admin)
Control=path of the control file
SQLLDR Syntax Flow
1.
The control file starts with a clause called “load
data.”
2.
We need to specify the path using the “infile”
clause
3.
By using “into table table_name” we are allowed
to move flat file data to an Oracle DB table.
4.
Insert/append/truncate/replace
5.
For an empty table, we are going to use “insert”
clause
6.
Fields terminated by ‘delimiter name.’
7.
Optionally enclosed by ‘delimiter name.’
8.
Trailing nullcols
9.
(col1,col2,col3)
10. And
save the control file by using .ctl extension
load data
infile 'path of flatfile'
insert/append/truncate/replace
into table table
fields terminated by 'delimitername'
optionally enclosed by 'delimiternme'
trailing nullcols
(col1,col2,co13)
In command line:
Sqlldr userid=username/password
Control=path of control file
Take /create one flat file in local C drive
Save the file with extension .text
Copy the path fo the flat file
Create table emp12
(
loc varchar2(20)
);
Create a table in Oracle DB and it
should be empty
Create “Control File” in the path of flat file(means flat
file and control file=same path)
For inserting data into target table(if the table is empty)
Now, open Command prompt as an administrator.
Use cd.. to go the particular path or use cd <enter path
here> to go specific path.
Sqlldr userid=username/password(for your SQL developer
tool)
Control=<here we need to enter the path of the
control file>
Commands need to be executed to run the SQL *Loader
Success case we will get the following
screen:
It says total 190 records inserted to emp table
successfully.
Syntax for Appending the data
or updating the data table where data already exists
Apend Clause(use to add extra rows or update the rows to
existing data in table)
load data
infile *
append
into table target
fields terminated by
(empno, ename, sal)
begindata
104, abc, 2000
105, xyz,3000
No comments:
Post a Comment