advertisements
_____________________________________________________________________________________________________________________
The BAD parameter specifies the name of bad
file created by SQL*Loader to store the records that rejected during the insert
process because of the data type mismatch or improper format. If the file name
is not specified, by default the SQL*Loader will take the .bad
as the file name. If you specify the bad file name with parameter bad then it
will override the default file name. See some examples below.
My data file content
is as below. I have 2 bad records which are having datatype mismatch marked in
red color below.
$ cat sqlload.dat
10,ACCOUNTING,NEW
YORK
2D,RESEARCH,DALLAS
30,SALES,CHICAGO
4D,OPERATIONS,BOSTON
My Control file
content is
$ cat sqlload.ctl
load data
infile
'/home/oracle/st/sqlload.dat'
into table dept
fields terminated by
","
(DEPTNO,DNAME,LOC)
SQL Loader command
sqlldr sthomas/tiger
control=/home/oracle/st/sqlload.ctl
SQL*Loader: Release
11.2.0.3.0 - Production on Tue May 28 10:20:41 2013
Copyright (c) 1982,
2011, Oracle and/or its affiliates. All
rights reserved.
Commit point reached
- logical record count 4
Bad file created
with name sqlload.bad. It is taken the by default name(controlfile.bad).
$ ls -ltr
total 20
-rw-r--r-- 1 oracle
oinstall 80 May 28 10:11 sqlload.dat
-rw-r--r-- 1 oracle
oinstall 107 May 28 10:15 sqlload.ctl
-rw-r--r-- 1 oracle
oinstall 1767 May 28 10:20 sqlload.log
-rw-r--r--
1 oracle oinstall 40 May 28 10:20
sqlload.bad
$ cat sqlload.log
SQL*Loader: Release
11.2.0.3.0 - Production on Tue May 28 10:20:41 2013
Copyright (c) 1982,
2011, Oracle and/or its affiliates. All
rights reserved.
Control File: /home/oracle/st/sqlload.ctl
Data File: /home/oracle/st/sqlload.dat
Bad File: /home/oracle/st/sqlload.bad
Discard File:
none specified
(Allow all discards)
Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 64 rows, maximum of 256000 bytes
Continuation: none specified
Path used: Conventional
Table DEPT, loaded
from every logical record.
Insert option in
effect for this table: INSERT
Column Name Position Len
Term Encl Datatype
------------------------------
---------- ----- ---- ---- ---------------------
DEPTNO FIRST *
, CHARACTER
DNAME NEXT *
, CHARACTER
LOC NEXT *
, CHARACTER
Record 2: Rejected -
Error on table DEPT, column DEPTNO.
ORA-01722: invalid
number
Record 4: Rejected -
Error on table DEPT, column DEPTNO.
ORA-01722: invalid
number
Table DEPT:
2 Rows successfully loaded.
2 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: 49536
bytes(64 rows)
Read buffer bytes: 1048576
Total logical
records skipped: 0
Total logical
records read: 4
Total
logical records rejected: 2
Total logical
records discarded: 0
Run began on Tue May
28 10:20:41 2013
Run ended on Tue May
28 10:20:41 2013
Elapsed time
was: 00:00:00.11
CPU time was: 00:00:00.01
In below example I
am using the bad parameter for specifying the file name. This will override the
default bad file name.
$ sqlldr sthomas/tiger
control=/home/oracle/st/sqlload.ctl bad=/home/oracle/st/badrec
SQL*Loader: Release
11.2.0.3.0 - Production on Tue May 28 10:22:42 2013
Copyright (c) 1982,
2011, Oracle and/or its affiliates. All
rights reserved.
Commit point reached
- logical record count 4
$ ls -lr
total 24
-rw-r--r-- 1 oracle
oinstall 1766 May 28 10:22 sqlload.log
-rw-r--r-- 1 oracle
oinstall 80 May 28 10:11 sqlload.dat
-rw-r--r-- 1 oracle
oinstall 107 May 28 10:15 sqlload.ctl
-rw-r--r-- 1 oracle
oinstall 40 May 28 10:20 sqlload.bad
-rw-r--r--
1 oracle oinstall 40 May 28 10:22
badrec.bad
_____________________________________________________________________________________________________________________
0 comments:
Post a Comment