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.
SQL*Loader provides the following methods to load data :-
Conventional path loads
Direct path loads
External table loads
To execute the SQL*Load tool, you need at least three files :-
The input data file stores delimited or raw data.
The parameter file stores the location of the input/output files.
The control file contains the specification on how data is loaded.
The target table should already be created otherwise ORA-04043 will be raised.
For INSERT option,the table must be empty otherwise below error will be raised :::
SQL*Loader-601: For INSERT option, table must be empty. The BAD FILE contains rows that were rejected because of errors. These errors might include bad datatypes or referential integrity constraints.
The DISCARD FILE contains rows that were discarded because they were filtered out because of a statement in the SQL*Loader control file.
DIRECT PATH INSERT
SQL> CREATE TABLE emails
2 (
3 email_id NUMBER PRIMARY KEY,
4 email VARCHAR2(150) NOT NULL
5 );
Table created.
SQL> SELECT * FROM emails;
no rows selectedINPUT FILE (email.dat) :-
1,john.doe@example.com
2,jane.doe@example.com
3,peter.doe@example.com
CONTROL FILE (email.ctl) :-
load data into table emails
insert
fields terminated by “,”
(
email_id,
email
)
PARAMETER FILE (email.par) :-
userid=course/course2@orcl
control=email.ctl
log=email.log
bad=email.bad
discard=email.dscrd
data=email.dat
direct=true
D:\TEST_PERSONAL>sqlldr parfile=email.par
SQL*Loader: Release 19.0.0.0.0 - Production on Wed May 25 14:29:40 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
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.
SQL> SELECT * FROM emails;
EMAIL_ID EMAIL
---------- ----------------------
1 john.doe@example.com
2 jane.doe@example.com
3 peter.doe@example.com
LOG FILE (email.log) :-
SQL*Loader: Release 19.0.0.0.0 - Production on Wed May 25 14:29:40 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Control File: email.ctl
Data File: email.dat
Bad File: email.bad
Discard File: email.dscrd
(Allow all discards)
Number to load: ALL
Number to skip: 0
Errors allowed: 50
Continuation: none specified
Path used: Direct
Table EMAILS, loaded from every logical record.
Insert option in effect for this table: INSERT
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
EMAIL_ID FIRST * , CHARACTER
EMAIL NEXT * , CHARACTER
The following index(es) on table EMAILS were processed:
index COURSE.SYS_C009122 loaded successfully with 3 keys
Table EMAILS:
3 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.
Bind array size not used in direct path.
Column array rows : 5000
Stream buffer bytes: 256000
Read buffer bytes: 1048576
Total logical records skipped: 0
Total logical records read: 3
Total logical records rejected: 0
Total logical records discarded: 0
Total stream buffers loaded by SQL*Loader main thread: 2
Total stream buffers loaded by SQL*Loader load thread: 0
Run began on Wed May 25 14:29:40 2022
Run ended on Wed May 25 14:29:43 2022
Elapsed time was: 00:00:02.57
CPU time was: 00:00:00.13CONVENTIONAL PATH INSERT
SQL> CREATE TABLE emails
2 (
3 email_id NUMBER PRIMARY KEY,
4 email VARCHAR2(150) NOT NULL
5 );
Table created.
SQL> SELECT * FROM emails;INPUT FILE (email.dat) :-
1,john.doe@example.com
2,jane.doe@example.com
3,peter.doe@example.com
CONTROL FILE (email.ctl) :-
load data into table emails
insert
fields terminated by “,”
(
email_id,
email
)
PARAMETER FILE (email.par) :-
userid=course/course2@orcl
control=email.ctl
log=email.log
bad=email.bad
discard=email.dscrd
data=email.dat
direct=false
D:\TEST_PERSONAL>sqlldr parfile=email.par
SQL*Loader: Release 19.0.0.0.0 - Production on Wed May 25 14:37:41 2022
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 2
Commit point reached - logical record count 3
Table EMAILS:
3 Rows successfully loaded.
Check the log file:
email.log
for more information about the load.
SQL> SELECT * FROM emails;
EMAIL_ID EMAIL
---------- ----------------------
1 john.doe@example.com
2 jane.doe@example.com
3 peter.doe@example.com
LOG FILE (email.log) :-
SQL*Loader: Release 19.0.0.0.0 - Production on Wed May 25 14:37:41 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Control File: email.ctl
Data File: email.dat
Bad File: email.bad
Discard File: email.dscrd
(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 EMAILS, loaded from every logical record.
Insert option in effect for this table: INSERT
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
EMAIL_ID FIRST * , CHARACTER
EMAIL NEXT * , CHARACTER
Table EMAILS:
3 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: 3
Total logical records rejected: 0
Total logical records discarded: 0
Run began on Wed May 25 14:37:41 2022
Run ended on Wed May 25 14:37:42 2022
Elapsed time was: 00:00:00.94
CPU time was: 00:00:00.13
