SQL * Loader
CHAPTER 9-SQL * Loader
v SQL*Loader allows you to load data from an external file into a table in the database. It can parse many delimited file formats such as CSV, tab-delimited, and pipe-delimited.
v
Method 1 by
using par file in command prompt
v We will load email data CSV file format into the emails table in
the database.
v The following is the content of the email.dat file:
1, john.doe@example.com
2, jane.doe@example.com
3, peter.doe@example.com
v The contents of the control file (email.ctl) is as follows:
load data into table emails
insert
fields terminated by ","
(
email_id,
email
)
In the
control file:
The load data into table emails
insert instruct
the SQL*Loader to load data into the emails table using the
INSERT statement.
The fields terminated
by "," (email_id,email) specifies that each row in the file has two
columns email_id and email separated by
a comma (,).
v Here is
the content of the parameter file (email.par):
userid=system/1234
control=email.ctl
log=email.log
bad=email.bad
data=email.dat
direct=true
In this parameter file, we specify the user (userid) that
will connect to the Oracle database, the control file (email.ctl), log
file (email.log), and data file (email.dat).
The email.bad file
stores invalid data. And the last line direct=true instructs
the SQL*Loader to use the direct path load method.
v
First, create a new table to store the
email data from the input datafile:
CREATE TABLE emails(
email_id
NUMBER PRIMARY KEY,
email
VARCHAR2(150) NOT NULL
);
v
Open command prompt simply text
>sqlldr parfile=email.par
v
And this output display in Command prompt
Path used: Direct
Load completed - logical record count 3.
Table EMAILS:
3 Rows
successfully loaded.
Check the log file:
email.log
for more information about the load.
v
Method 2 by
using ctl file in command prompt
v 1st create students.csv file like below
1,kamal
2,raj
3,ramesh
4,mahesh
v Create students.ctl file like below
load data
infile 'students.csv'
insert into table students
fields terminated by ","
Trailing NULLCOLS
(
roll_no,
name
)
v Create table students
CREATE TABLE STUDENTS
(
ROLL_NO NUMBER (2),
NAME VARCHAR2 (20)
);
v Then open command prompt, then take the location where
csv, ctl files located by using this commands
C:\Users\KAMAL>cd\
C:\>E:
E:\> cd E:\SQL loader
E:\SQL loader>
v Then execute command
E:\SQL loader>sqlldr system/1234
students.ctl
Here system is user and 1234 is password
of database.
v Then it display result as
SQL*Loader: Release 19.0.0.0.0 -
Production on Wed Jun 9 13:53:18 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or
its affiliates. All rights reserved.
Path used: Conventional
Commit point reached - logical record
count 3
Commit point reached - logical record
count 4
Table STUDENTS:
4
Rows successfully loaded.
Check the log file:
students.log
for more information about the load.
v Log file generated automatically when process
complete, students.log file look like as below
SQL*Loader: Release 19.0.0.0.0 -
Production on Wed Jun 9 13:53:18 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or
its affiliates. All rights reserved.
Control File: students.ctl
Data File: students.csv
Bad File: students.bad
Discard File: none specified
(Allow all discards)
Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 250 rows, maximum of 1048576 bytes
Continuation: none specified
Path used: Conventional
Table STUDENTS, loaded from every logical
record.
Insert option in effect for this table:
INSERT
TRAILING NULLCOLS option in effect
Column Name Position Len
Term Encl Datatype
------------------------------ ----------
----- ---- ---- ---------------------
ROLL_NO FIRST *
, CHARACTER
NAME NEXT *
, CHARACTER
Table STUDENTS:
4
Rows successfully loaded.
0
Rows not loaded due to data errors.
0
Rows not loaded because all WHEN clauses were failed.
0
Rows not loaded because all fields were null.
Space allocated for bind array: 129000 bytes(250 rows)
Read
buffer bytes: 1048576
Total logical records skipped: 0
Total logical records read: 4
Total logical records rejected: 0
Total logical records discarded: 0
Run began on Wed Jun 09 13:53:18 2021
Run ended on Wed Jun 09 13:53:20 2021
Elapsed time was: 00:00:01.60
CPU time was: 00:00:00.12
v If there is error occurs then it automatically
generate ‘.bad’, ‘.dis’ files.
v .bad file means it contain records that were reject by
SQL * Loader.
v .dis file means the records read from input file but
not inserted in table, these records saved in this.
Comments
Post a Comment