SQL Loader bad file vs Discard File: Difference and Examples

advertisements

_____________________________________________________________________________________________________________________

BAD File contains the records which are rejected either by SQL Loader or by the database because of the bad formatting or data type mismatch.

More details and example here.

DISCARD file is to store the records that are neither inserted into table nor rejected as bad. This is an optional parameter with SQL Loader and by default name would be .dsc.
As the name indicates these records are discarded by the SQL Loader because they were filtered out with the record selection criteria mentioned in the control file commands. This file creates only when needed. You can specify the maximum number of discard records that can accept by the discard file by using DISCARDMAX parameter.

First preference goes to discard file even though there is a bad record. You cans see the example below.

Example:

My table description is

SQL>  desc dept

 Name              Null?    Type

 ----------------- -------- ------------

 DEPTNO                     NUMBER(2)

 DNAME                      VARCHAR2(14)

 LOC                        VARCHAR2(13)

Here is my data file which has 2 bad records marked in red color. Those records will not match the data type in the table.

$ cat sqlload.dat

10,ACCOUNTING,NEW YORK

2D,RESEARCH,DALLAS

30,SALES,CHICAGO

4D,OPERATIONS,BOSTON

50,HUMAN RESOURCE,BOSTON

60,IT,BOSTON

70,PRODUCTION,DALLAS

80,QUALITY,BOSTON

My control file is as follows. In my control file I have mentioned condition to insert the records with location not equal to BOSTON. So those filtered records will go to the discard file.

cat sqlload.ctl

load data

infile '/home/oracle/st/sqlload.dat'

badfile '/home/oracle/st/badrec.bad'

discardfile '/home/oracle/st/dicardload.dsc'

into table dept

WHEN LOC!='BOSTON'

fields terminated by ","

(DEPTNO,DNAME,LOC)

Executing the 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 11:36:13 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 8

Now let’s check the content of the discard file and bad file. The first bad record marked to the discard file because of the filtration. As I said earlier the preference goes to the filtration. All the records with location BOSTON got filtered to the discard file.

$ cat dicardload.dsc

4D,OPERATIONS,BOSTON

50,HUMAN RESOURCE,BOSTON

60,IT,BOSTON

80,QUALITY,BOSTON

$ cat badrec.bad

2D,RESEARCH,DALLAS

I think it helped you!!!

_____________________________________________________________________________________________________________________

Website Stats