Oracle SQL Loader Bad file (.bad) Parameter Example

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

DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google