# we will learn the sql loader
# SQL*Loader is a bulk loader utility used for moving data from external files into the Oracle database
# move the file emp.csv to server /u01/app/oracle
show user
show con_name
/*
USER is "HR"
CON_NAME
------------------------------
ORCLPDB
*/
CREATE TABLE EMP_LOAD
(EMPNO NUMBER ,
FNAME VARCHAR2(100),
LNAME VARCHAR2(100)
);
/*
Table EMP_LOAD created.
*/
SELECT * FROM EMP_LOAD;
/*
no rows selected
*/
# we have file 12.1_174_emp.csv in /u01/app/oracle
# we want to move the data from this file to the table EMP_LOAD
# we use SQL*Loader
# we need to do a file called conrol file .ctl ( make a emp.ctl )
/*
Load Data
INFILE '/u01/app/oracle/12.1_174_emp.csv'
APPEND
INTO Table emp_load
FIELDS TERMINATED BY ','
(empno,
fname,
lname
)
*/
/*
[oracle@test oracle]$ touch emp.ctl
[oracle@test oracle]$ ll
합계 504
-rw------- 1 oracle oinstall 510072 12월 19 15:40 12.1_174_emp.csv
drwxr-x--- 4 oracle oinstall 30 11월 29 22:24 admin
drwxr-x--- 4 oracle oinstall 30 11월 29 22:26 audit
drwxr-x--- 5 oracle oinstall 47 11월 11 01:02 cfgtoollogs
drwxr-xr-x 2 oracle oinstall 6 11월 11 00:58 checkpoints
drwxrwxr-x 23 oracle oinstall 4096 11월 11 00:58 diag
-rw-r--r-- 1 oracle oinstall 0 12월 19 15:51 emp.ctl <<< 파일 생성
drwxr-x--- 3 oracle oinstall 18 11월 29 22:24 fast_recovery_area
drwxr-x--- 4 oracle oinstall 65 11월 29 22:24 oradata
drwxrwxr-x 3 oracle oinstall 16 11월 11 00:10 product
[oracle@test oracle]$ vim emp.ctl <<< 명령어 입력
[oracle@test oracle]$
[oracle@test oracle]$ cat emp.ctl
Load Data
INFILE '/u01/app/oracle/12.1_174_emp.csv'
APPEND
INTO Table emp_load
FIELDS TERMINATED BY ','
(empno,
fname,
lname
)
[oracle@test oracle]$
*/
# then after this we execute this commnad sqlldr control=/u01/app/oracle/emp.ctl log=/u01/app/oracle/emp.log
# go to vm
# and do this sqlldr control=/u01/app/oracle/emp.ctl log=/u01/app/oracle/emp.log
[oracle@test oracle]$ sqlldr control=/u01/app/oracle/emp.ctl log=/u01/app/oracle/emp.log
Username:hr/hr@orclpdb
SQL*Loader: Release 19.0.0.0.0 - Production on Tue Dec 19 15:57:17 2023
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 250
Commit point reached - logical record count 500
Commit point reached - logical record count 750
Commit point reached - logical record count 1000
Commit point reached - logical record count 1250
Commit point reached - logical record count 1500
Commit point reached - logical record count 1750
Commit point reached - logical record count 2000
Commit point reached - logical record count 2250
Commit point reached - logical record count 2500
Commit point reached - logical record count 2750
Commit point reached - logical record count 3000
Commit point reached - logical record count 3250
Commit point reached - logical record count 3500
Commit point reached - logical record count 3750
Commit point reached - logical record count 4000
Commit point reached - logical record count 4250
Commit point reached - logical record count 4500
Commit point reached - logical record count 4750
Commit point reached - logical record count 5000
Commit point reached - logical record count 5250
Commit point reached - logical record count 5500
Commit point reached - logical record count 5750
Commit point reached - logical record count 6000
Commit point reached - logical record count 6250
Commit point reached - logical record count 6500
Commit point reached - logical record count 6750
Commit point reached - logical record count 7000
Commit point reached - logical record count 7250
Commit point reached - logical record count 7500
Commit point reached - logical record count 7750
Commit point reached - logical record count 8000
Commit point reached - logical record count 8250
Commit point reached - logical record count 8500
Commit point reached - logical record count 8750
Commit point reached - logical record count 9000
Commit point reached - logical record count 9250
Commit point reached - logical record count 9500
Commit point reached - logical record count 9750
Commit point reached - logical record count 10000
Commit point reached - logical record count 10250
Commit point reached - logical record count 10500
Commit point reached - logical record count 10750
Commit point reached - logical record count 11000
Commit point reached - logical record count 11250
Commit point reached - logical record count 11500
Commit point reached - logical record count 11750
Commit point reached - logical record count 12000
Commit point reached - logical record count 12250
Commit point reached - logical record count 12500
Commit point reached - logical record count 12750
Commit point reached - logical record count 13000
Commit point reached - logical record count 13250
Commit point reached - logical record count 13500
Commit point reached - logical record count 13750
Commit point reached - logical record count 14000
Commit point reached - logical record count 14250
Commit point reached - logical record count 14500
Commit point reached - logical record count 14750
Commit point reached - logical record count 15000
Commit point reached - logical record count 15250
Commit point reached - logical record count 15500
Commit point reached - logical record count 15750
Commit point reached - logical record count 16000
Commit point reached - logical record count 16125
Table EMP_LOAD:
16125 Rows successfully loaded.
Check the log file:
/u01/app/oracle/emp.log
for more information about the load.
[oracle@test oracle]$
[oracle@test oracle]$ cat emp.log
SQL*Loader: Release 19.0.0.0.0 - Production on Tue Dec 19 15:57:17 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Control File: /u01/app/oracle/emp.ctl
Data File: /u01/app/oracle/12.1_174_emp.csv
Bad File: /u01/app/oracle/12.1_174_emp.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 EMP_LOAD, loaded from every logical record.
Insert option in effect for this table: APPEND
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
EMPNO FIRST * , CHARACTER
FNAME NEXT * , CHARACTER
LNAME NEXT * , CHARACTER
Table EMP_LOAD:
16125 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: 193500 bytes(250 rows)
Read buffer bytes: 1048576
Total logical records skipped: 0
Total logical records read: 16125
Total logical records rejected: 0
Total logical records discarded: 0
Run began on Tue Dec 19 15:57:17 2023
Run ended on Tue Dec 19 15:57:31 2023
Elapsed time was: 00:00:13.82
CPU time was: 00:00:00.04
[oracle@test oracle]$
*/
col fname for a20
col lname for a20
SELECT * FROM EMP_LOAD;
/*
EMPNO FNAME LNAME
---------- -------------------- --------------------
126953 Irene953 Mikkilineni953
126954 Irene954 Mikkilineni954
126955 Irene955 Mikkilineni955
...
*/
# 오류 없이 잘 입력된것을 로그 파일에서 알 수 있다.
truncate table EMP_LOAD;
/*
Table EMP_LOAD truncated.
*/
# now update the file emp.csv, make some ids to be characters
# we want to know the bad file, the records that not inserted
# sqlldr control=/u01/app/oracle/emp.ctl log=/u01/app/oracle/emp.log
# 두번째 라인의 첫 컬럼데이터를 숫자에서 문자로 변경해야 오류를 발생 시킬 예정.
[oracle@test oracle]$ cat 12.1_174_emp.csv | more
1001,Steven1,King1
abc,Steven2,King2
1003,Steven3,King3
1004,Steven4,King4
1005,Steven5,King5
[oracle@test oracle]$ sqlldr control=/u01/app/oracle/emp.ctl log=/u01/app/oracle/emp.log
Username:hr/hr@orclpdb
SQL*Loader: Release 19.0.0.0.0 - Production on Tue Dec 19 16:15:58 2023
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 250
Commit point reached - logical record count 500
...
Commit point reached - logical record count 15750
Commit point reached - logical record count 16000
Commit point reached - logical record count 16125
Table EMP_LOAD:
16124 Rows successfully loaded.
Check the log file:
/u01/app/oracle/emp.log
for more information about the load.
[oracle@test oracle]$
# 로그파일에 보면 오류가 1개 발생한것을 확인 할 수 있다.
[oracle@test oracle]$ cat emp.log
SQL*Loader: Release 19.0.0.0.0 - Production on Tue Dec 19 16:15:58 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Control File: /u01/app/oracle/emp.ctl
Data File: /u01/app/oracle/12.1_174_emp.csv
Bad File: /u01/app/oracle/12.1_174_emp.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 EMP_LOAD, loaded from every logical record.
Insert option in effect for this table: APPEND
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
EMPNO FIRST * , CHARACTER
FNAME NEXT * , CHARACTER
LNAME NEXT * , CHARACTER
Record 2: Rejected - Error on table EMP_LOAD, column EMPNO.
ORA-01722: invalid number
Table EMP_LOAD:
16124 Rows successfully loaded.
1 Row 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: 193500 bytes(250 rows)
Read buffer bytes: 1048576
Total logical records skipped: 0
Total logical records read: 16125
Total logical records rejected: 1 <<<<<<<<<<<<<<<<<<<<<<<<<<<<
Total logical records discarded: 0
Run began on Tue Dec 19 16:15:58 2023
Run ended on Tue Dec 19 16:16:07 2023
Elapsed time was: 00:00:08.47
CPU time was: 00:00:00.05
[oracle@test oracle]$
# 오류가 발생한 데이터 라인을 별도의 파일에 저정되어 있다.
# .bad 파일 확인
[oracle@test oracle]$ cat 12.1_174_emp.bad
abc,Steven2,King2
[oracle@test oracle]$
*/