INSERT APPEND
INPUT FILE (email.dat) :-
4,111.doe@example.com
5,222.die@example.com
CONTROL FILE (email.ctl) :-
load data into table emails
append
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:46:32 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 1
Commit point reached - logical record count 2
Table EMAILS:
2 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
4 111.doe@example.com
5 222.die@example.com
LOG FILE (email.log) :-
SQL*Loader: Release 19.0.0.0.0 - Production on Wed May 25 14:46:32 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: APPEND
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
EMAIL_ID FIRST * , CHARACTER
EMAIL NEXT * , CHARACTER
Table EMAILS:
2 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: 2
Total logical records rejected: 0
Total logical records discarded: 0
Run began on Wed May 25 14:46:32 2022
Run ended on Wed May 25 14:46:32 2022
Elapsed time was: 00:00:00.16
CPU time was: 00:00:00.11BEGINDATA
You can also specify the data directly inside the control file itself using BEGINDATA keyword. i.e Anything that comes after BEGINDATA will be treated as data to be uploaded to the table.
INPUT FILE (email.dat) :-
6,333.doe@example.com
7,444.die@example.com
CONTROL FILE (email.ctl) :-
load data into table emails
append
fields terminated by ","
(
email_id,
email
)
begindata
6,333.doe@example.com
7,444.die@example.com
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:54:47 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
SQL*Loader-275: Data is in control file but "INFILE *" has not been specified.
Path used: Conventional
Commit point reached - logical record count 1
Commit point reached - logical record count 2
Table EMAILS:
2 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
4 111.doe@example.com
5 222.die@example.com
6 333.doe@example.com
7 444.die@example.com
7 rows selected.
LOG FILE (email.log) :-
SQL*Loader: Release 19.0.0.0.0 - Production on Wed May 25 14:54:47 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
SQL*Loader-275: Data is in control file but "INFILE *" has not been specified.
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: APPEND
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
EMAIL_ID FIRST * , CHARACTER
EMAIL NEXT * , CHARACTER
Table EMAILS:
2 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: 2
Total logical records rejected: 0
Total logical records discarded: 0
Run began on Wed May 25 14:54:47 2022
Run ended on Wed May 25 14:54:47 2022
Elapsed time was: 00:00:00.25
CPU time was: 00:00:00.11NORMAL CONVENTIONAL INSERT
SQL> CREATE TABLE employee
2 (
3 id INTEGER,
4 name VARCHAR2(10),
5 dept VARCHAR2(15),
6 salary INTEGER,
7 hiredon DATE
8 );
Table created.
SQL> SELECT * FROM employee;
no rows selectedINPUT FILE (employee.txt) :-
100,Thomas,Sales,5000
200,Jason,Technology,5500
300,Mayla,Technology,7000
400,Nisha,Marketing,9500
500,Randy,Technology,6000
501,Ritu,Accounting,5400
CONTROL FILE (employee.ctl) :-
load data
infile 'D:\TEST_PERSONAL\employee.txt'
into table employee
fields terminated by ","
( id, name, dept, salary )
D:\TEST_PERSONAL>sqlldr course/course2 control=D:\TEST_PERSONAL\employee.ctl
SQL*Loader: Release 19.0.0.0.0 - Production on Wed May 25 15:19:43 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 5
Commit point reached - logical record count 6
Table EMPLOYEE:
6 Rows successfully loaded.
Check the log file:
employee.log
for more information about the load.
SQL> SELECT * FROM employee;
ID NAME DEPT SALARY HIREDON
---------- ---------- --------------- ---------- ---------
100 Thomas Sales 5000
200 Jason Technology 5500
300 Mayla Technology 7000
400 Nisha Marketing 9500
500 Randy Technology 6000
501 Ritu Accounting 5400
6 rows selected.
LOGFILE (employee.log) :-
SQL*Loader: Release 19.0.0.0.0 - Production on Wed May 25 15:19:43 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Control File: D:\TEST_PERSONAL\employee.ctl
Data File: D:\TEST_PERSONAL\employee.txt
Bad File: D:\TEST_PERSONAL\employee.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 EMPLOYEE, loaded from every logical record.
Insert option in effect for this table: INSERT
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
ID FIRST * , CHARACTER
NAME NEXT * , CHARACTER
DEPT NEXT * , CHARACTER
SALARY NEXT * , CHARACTER
Table EMPLOYEE:
6 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: 258000 bytes(250 rows)
Read buffer bytes: 1048576
Total logical records skipped: 0
Total logical records read: 6
Total logical records rejected: 0
Total logical records discarded: 0
Run began on Wed May 25 15:19:43 2022
Run ended on Wed May 25 15:19:43 2022
Elapsed time was: 00:00:00.24
CPU time was: 00:00:00.16DATE FORMAT AND DIFFERENT DELIMITER
INPUT FILE (employee.txt) :-
100,Thomas$Sales^5000,31-JAN-2008
200,Jason$Technology^5500,01-Feb-2005
300,Mayla$Technology^7000,10-Aug-2000
400,Nisha$Marketing^9500,12-Dec-2011
500,Randy$Technology^6000,01-JAN-2007
CONTROL FILE (employee.ctl) :-
load data
infile 'D:\TEST_PERSONAL\employee.txt'
into table employee
fields terminated by ","
( id, name terminated by "$", dept terminated by "^", salary, hiredon DATE "dd-mon-yyyy" )
SQL> SELECT * FROM employee;
no rows selected
D:\TEST_PERSONAL>sqlldr course/course2 control=D:\TEST_PERSONAL\employee.ctl
SQL*Loader: Release 19.0.0.0.0 - Production on Wed May 25 15:26:28 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 4
Commit point reached - logical record count 5
Table EMPLOYEE:
5 Rows successfully loaded.
Check the log file:
employee.log
for more information about the load.
SQL> SELECT * FROM employee;
ID NAME DEPT SALARY HIREDON
---------- ---------- --------------- ---------- ---------
100 Thomas Sales 5000 31-JAN-08
200 Jason Technology 5500 01-FEB-05
300 Mayla Technology 7000 10-AUG-00
400 Nisha Marketing 9500 12-DEC-11
500 Randy Technology 6000 01-JAN-07
LOGFILE (employee.log) :-
SQL*Loader: Release 19.0.0.0.0 - Production on Wed May 25 15:26:28 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Control File: D:\TEST_PERSONAL\employee.ctl
Data File: D:\TEST_PERSONAL\employee.txt
Bad File: D:\TEST_PERSONAL\employee.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 EMPLOYEE, loaded from every logical record.
Insert option in effect for this table: INSERT
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
ID FIRST * , CHARACTER
NAME NEXT * $ CHARACTER
DEPT NEXT * ^ CHARACTER
SALARY NEXT * , CHARACTER
HIREDON NEXT * , DATE dd-mon-yyyy
Table EMPLOYEE:
5 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: 322500 bytes(250 rows)
Read buffer bytes: 1048576
Total logical records skipped: 0
Total logical records read: 5
Total logical records rejected: 0
Total logical records discarded: 0
Run began on Wed May 25 15:26:28 2022
Run ended on Wed May 25 15:26:28 2022
Elapsed time was: 00:00:00.17
CPU time was: 00:00:00.14FIXED LENGTH DATA UPLOAD
INPUT FILE (employee.txt) :-
200JasonTechnology5500
300MaylaTechnology7000
400NishaTechnology9500
500RandyTechnology6000
CONTROL FILE (employee.ctl) :-
load data
infile 'D:\TEST_PERSONAL\employee.txt'
into table employee
fields terminated by ","
( id position(1:3), name position(4:8), dept position(9:18), salary position(19:22) )
SQL> SELECT * FROM employee;
no rows selected
D:\TEST_PERSONAL>sqlldr course/course2 control=D:\TEST_PERSONAL\employee.ctl
SQL*Loader: Release 19.0.0.0.0 - Production on Wed May 25 15:30:33 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 3
Commit point reached - logical record count 4
Table EMPLOYEE:
4 Rows successfully loaded.
Check the log file:
employee.log
for more information about the load.
SQL> SELECT * FROM employee;
ID NAME DEPT SALARY HIREDON
---------- ---------- --------------- ---------- ---------
200 Jason Technology 5500
300 Mayla Technology 7000
400 Nisha Technology 9500
500 Randy Technology 6000
LOGFILE (employee.log) :-
SQL*Loader: Release 19.0.0.0.0 - Production on Wed May 25 15:30:33 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Control File: D:\TEST_PERSONAL\employee.ctl
Data File: D:\TEST_PERSONAL\employee.txt
Bad File: D:\TEST_PERSONAL\employee.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 EMPLOYEE, loaded from every logical record.
Insert option in effect for this table: INSERT
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
ID 1:3 3 , CHARACTER
NAME 4:8 5 , CHARACTER
DEPT 9:18 10 , CHARACTER
SALARY 19:22 4 , CHARACTER
Table EMPLOYEE:
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: 8000 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 May 25 15:30:33 2022
Run ended on Wed May 25 15:30:34 2022
Elapsed time was: 00:00:00.19
CPU time was: 00:00:00.09CHANGE THE DATA DURING UPLOAD
INPUT FILE (employee.txt) :-
100,Thomas,Sales,5000
200,Jason,Technology,5500
300,Mayla,Technology,7000
400,Nisha,Marketing,9500
500,Randy,Technology,6000
501,Ritu,Accounting,5400
CONTROL FILE (employee.ctl) :-
load data
infile 'D:\TEST_PERSONAL\employee.txt'
into table employee
fields terminated by ","
( id ":id+999",
name "upper(:name)",
dept "decode(:dept,'Technology','Techies', :dept)",
salary
)
SQL> SELECT * FROM employee;
no rows selected
D:\TEST_PERSONAL>sqlldr course/course2 control=D:\TEST_PERSONAL\employee.ctl
SQL*Loader: Release 19.0.0.0.0 - Production on Wed May 25 15:34:51 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 5
Commit point reached - logical record count 6
Table EMPLOYEE:
6 Rows successfully loaded.
Check the log file:
employee.log
for more information about the load.
SQL> SELECT * FROM employee;
ID NAME DEPT SALARY HIREDON
---------- ---------- --------------- ---------- ---------
1099 THOMAS Sales 5000
1199 JASON Techies 5500
1299 MAYLA Techies 7000
1399 NISHA Marketing 9500
1499 RANDY Techies 6000
1500 RITU Accounting 5400
6 rows selected.
LOGFILE (employee.log) :-
SQL*Loader: Release 19.0.0.0.0 - Production on Wed May 25 15:34:51 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Control File: D:\TEST_PERSONAL\employee.ctl
Data File: D:\TEST_PERSONAL\employee.txt
Bad File: D:\TEST_PERSONAL\employee.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 EMPLOYEE, loaded from every logical record.
Insert option in effect for this table: INSERT
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
ID FIRST * , CHARACTER
SQL string for column : ":id+999"
NAME NEXT * , CHARACTER
SQL string for column : "upper(:name)"
DEPT NEXT * , CHARACTER
SQL string for column : "decode(:dept,'Technology','Techies', :dept)"
SALARY NEXT * , CHARACTER
Table EMPLOYEE:
6 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: 258000 bytes(250 rows)
Read buffer bytes: 1048576
Total logical records skipped: 0
Total logical records read: 6
Total logical records rejected: 0
Total logical records discarded: 0
Run began on Wed May 25 15:34:51 2022
Run ended on Wed May 25 15:34:51 2022
Elapsed time was: 00:00:00.25
CPU time was: 00:00:00.09LOAD DATA FROM MULTIPLE FILES
INPUT FILE (employee.txt) :-
100,Thomas,Sales,5000
200,Jason,Technology,5500
300,Mayla,Technology,7000
400,Nisha,Marketing,9500
500,Randy,Technology,6000
600,Ritu,Accounting,5400
INPUT FILE (employee2.txt) :-
700,Thomas,Sales,5000
800,Jason,Technology,5500
CONTROL FILE (employee.ctl) :-
load data
infile 'D:\TEST_PERSONAL\employee.txt'
infile 'D:\TEST_PERSONAL\employee2.txt'
into table employee
fields terminated by ","
( id, name, dept, salary )
SQL> SELECT * FROM employee;
no rows selected
D:\TEST_PERSONAL>sqlldr course/course2 control=D:\TEST_PERSONAL\employee.ctl
SQL*Loader: Release 19.0.0.0.0 - Production on Wed May 25 15:39:08 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 5
Commit point reached - logical record count 6
Commit point reached - logical record count 7
Commit point reached - logical record count 8
Table EMPLOYEE:
8 Rows successfully loaded.
Check the log file:
employee.log
for more information about the load.
SQL> SELECT * FROM employee;
ID NAME DEPT SALARY HIREDON
---------- ---------- --------------- ---------- ---------
100 Thomas Sales 5000
200 Jason Technology 5500
300 Mayla Technology 7000
400 Nisha Marketing 9500
500 Randy Technology 6000
600 Ritu Accounting 5400
700 Thomas Sales 5000
800 Jason Technology 5500
8 rows selected.
LOGFILE (employee.log) :-
SQL*Loader: Release 19.0.0.0.0 - Production on Wed May 25 15:39:08 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Control File: D:\TEST_PERSONAL\employee.ctl
There are 2 data files:
Data File: D:\TEST_PERSONAL\employee.txt
Bad File: D:\TEST_PERSONAL\employee.bad
Discard File: none specified
(Allow all discards)
Data File: D:\TEST_PERSONAL\employee2.txt
Bad File: D:\TEST_PERSONAL\employee2.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 EMPLOYEE, loaded from every logical record.
Insert option in effect for this table: INSERT
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
ID FIRST * , CHARACTER
NAME NEXT * , CHARACTER
DEPT NEXT * , CHARACTER
SALARY NEXT * , CHARACTER
Table EMPLOYEE:
8 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: 258000 bytes(250 rows)
Read buffer bytes: 1048576
Total logical records skipped: 0
Total logical records read: 8
Total logical records rejected: 0
Total logical records discarded: 0
Run began on Wed May 25 15:39:08 2022
Run ended on Wed May 25 15:39:08 2022
Elapsed time was: 00:00:00.17
CPU time was: 00:00:00.09LOAD DATA TO MULTIPLE TABLES
INPUT FILE (employee.txt) :-
100 Thomas Sales 5000 1000
200 Jason Technology 5500 2000
300 Mayla Technology 7000 2000
400 Nisha Marketing 9500 1000
500 Randy Technology 6000 3000
CONTROL FILE (employee.ctl) :-
load data
infile 'D:\TEST_PERSONAL\employee.txt'
into table employee
( id position(1:3),
name position(5:10),
dept position(12:21),
salary position(23:26))
into table bonus
( id position(1:3),
bonus position(28:31))
SQL> CREATE TABLE bonus
2 (
3 id integer,
4 bonus integer
5 );
Table created.
SQL> SELECT * FROM bonus;
no rows selected
SQL> SELECT * FROM employee;
no rows selected
D:\TEST_PERSONAL>sqlldr course/course2 control=D:\TEST_PERSONAL\employee.ctl
SQL*Loader: Release 19.0.0.0.0 - Production on Wed May 25 16:09:22 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 4
Commit point reached - logical record count 5
Table EMPLOYEE:
5 Rows successfully loaded.
Table BONUS:
5 Rows successfully loaded.
Check the log file:
employee.log
for more information about the load.
SQL> SELECT * FROM bonus;
ID BONUS
---------- ----------
100 1000
200 2000
300 2000
400 1000
500 3000
SQL> SELECT * FROM employee;
ID NAME DEPT SALARY HIREDON
---------- ---------- --------------- ---------- ---------
100 Thomas Sales 5000
200 Jason Technology 5500
300 Mayla Technology 7000
400 Nisha Marketing 9500
500 Randy Technology 6000
LOGFILE (employee.log) :-
SQL*Loader: Release 19.0.0.0.0 - Production on Wed May 25 16:09:22 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Control File: D:\TEST_PERSONAL\employee.ctl
Data File: D:\TEST_PERSONAL\employee.txt
Bad File: D:\TEST_PERSONAL\employee.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 EMPLOYEE, loaded from every logical record.
Insert option in effect for this table: INSERT
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
ID 1:3 3 CHARACTER
NAME 5:10 6 CHARACTER
DEPT 12:21 10 CHARACTER
SALARY 23:26 4 CHARACTER
Table BONUS, loaded from every logical record.
Insert option in effect for this table: INSERT
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
ID 1:3 3 CHARACTER
BONUS 28:31 4 CHARACTER
Table EMPLOYEE:
5 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.
Table BONUS:
5 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: 11000 bytes(250 rows)
Read buffer bytes: 1048576
Total logical records skipped: 0
Total logical records read: 5
Total logical records rejected: 0
Total logical records discarded: 0
Run began on Wed May 25 16:09:22 2022
Run ended on Wed May 25 16:09:23 2022
Elapsed time was: 00:00:00.20
CPU time was: 00:00:00.09LOAD SPECIFIC ROWS FROM A DATAFILE
INPUT FILE (employee.txt) :-
100,Thomas,Sales,5000
200,Jason,Technology,5500
300,Mayla,Technology,7000
400,Nisha,Marketing,9500
500,Randy,Technology,6000
600,Ritu,Accounting,5400
CONTROL FILE (employee.ctl) :-
load data
infile 'D:\TEST_PERSONAL\employee.txt'
into table employee
when dept = 'Technology'
fields terminated by ","
( id, name, dept, salary )
SQL> SELECT * FROM employee;
no rows selected
D:\TEST_PERSONAL>sqlldr course/course2 control=D:\TEST_PERSONAL\employee.ctl
SQL*Loader: Release 19.0.0.0.0 - Production on Wed May 25 15:49:08 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 5
Table EMPLOYEE:
3 Rows successfully loaded.
Check the log file:
employee.log
for more information about the load.
SQL> SELECT * FROM employee;
ID NAME DEPT SALARY HIREDON
---------- ---------- --------------- ---------- ---------
200 Jason Technology 5500
300 Mayla Technology 7000
500 Randy Technology 6000
LOGFILE (employee.log) :-
SQL*Loader: Release 19.0.0.0.0 - Production on Wed May 25 15:49:08 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Control File: D:\TEST_PERSONAL\employee.ctl
Data File: D:\TEST_PERSONAL\employee.txt
Bad File: D:\TEST_PERSONAL\employee.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 EMPLOYEE, loaded when DEPT = 0X546563686e6f6c6f6779(character 'Technology')
Insert option in effect for this table: INSERT
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
ID FIRST * , CHARACTER
NAME NEXT * , CHARACTER
DEPT NEXT * , CHARACTER
SALARY NEXT * , CHARACTER
Record 1: Discarded - failed all WHEN clauses.
Record 4: Discarded - failed all WHEN clauses.
Record 6: Discarded - failed all WHEN clauses.
Table EMPLOYEE:
3 Rows successfully loaded.
0 Rows not loaded due to data errors.
3 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
Space allocated for bind array: 258000 bytes(250 rows)
Read buffer bytes: 1048576
Total logical records skipped: 0
Total logical records read: 6
Total logical records rejected: 0
Total logical records discarded: 3
Run began on Wed May 25 15:49:08 2022
Run ended on Wed May 25 15:49:08 2022
Elapsed time was: 00:00:00.18
CPU time was: 00:00:00.09
