111 - [Oracle 19C] SQL Loader - discard file example

 

 

SQL loader Discard file
Discard file:  
The discard file contains rows that were discarded because they were filtered out 
because of a statement in the SQL*Loader control file ( when clause )

 

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  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 ( example emp.ctl )
# note: we will not load empno='1008'


[oracle@test oracle]$ cat emp.csv | more
1001,Steven1,King1
1002,Steven2,King2
1003,Steven3,King3
1004,Steven4,King4
1005,Steven5,King5
1006,Steven6,King6
1008,Steven8,King8
1009,Steven9,King9
10010,Steven10,King10
10011,Steven11,King11
10013,Steven13,King13
10014,Steven14,King14
....

 

 

 

# make emp.ctl file.

[oracle@test oracle]$ cat emp.ctl
Load Data
INFILE '/u01/app/oracle/emp.csv'
APPEND
INTO Table emp_load
WHEN empno <> '1008'
FIELDS TERMINATED BY ','
(empno,
fname,
lname
)
[oracle@test oracle]$

 

 

 

[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 Sun Dec 24 23:05:42 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 251
Commit point reached - logical record count 501
....
Commit point reached - logical record count 16001
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]$



SELECT * FROM EMP_LOAD where rownum < 10 order by 1;
/*
     EMPNO FNAME                LNAME               
---------- -------------------- --------------------
      1001 Steven1              King1
      1002 Steven2              King2
      1003 Steven3              King3
      1004 Steven4              King4
      1005 Steven5              King5
      1006 Steven6              King6
      1009 Steven9              King9
     10010 Steven10             King10
     10011 Steven11             King11
....
*/

 

 

 

truncate table EMP_LOAD;
/*
Table EMP_LOAD truncated.
*/



# when 절에서 제외시킨 항목들을 확인하는 옵션 "DISCARD=emp.dsc"
[oracle@test oracle]$ sqlldr hr/hr@orclpdb control=/u01/app/oracle/emp.ctl log=/u01/app/oracle/emp.log DISCARD=emp.dsc

SQL*Loader: Release 19.0.0.0.0 - Production on Sun Dec 24 23:10:32 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 251
Commit point reached - logical record count 501
....
Commit point reached - logical record count 16001
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]$ ls -ltr
합계 524
....
-rw-r--r--  1 oracle oinstall    140 12월 21 17:33 emp.ctl
-rw-------  1 oracle oinstall 510072 12월 21 17:37 emp.csv
-rw-r--r--  1 oracle oinstall     20 12월 24 23:10 emp.dsc
-rw-r--r--  1 oracle oinstall   1674 12월 24 23:10 emp.log
[oracle@test oracle]$

로그파일에서 1개의 라인이 discarded된 것을 확인할 수 있고 실제 데이터는 .dsc 파일에서 확인 가능함.
Total logical records discarded:        1
....


[oracle@test oracle]$ cat emp.dsc
1008,Steven8,King8
[oracle@test oracle]$