advertisements
_____________________________________________________________________________________________________________________
This parameter specifies the maximum number
of discard records can be allowed during a particular data load. This is a
number parameter and if you are not specifying any number for this argument
then it will consider all discard records. Otherwise it will exit the data load
once it reaches the limit. See an example below
Here is my data file
$ cat *dat
10,ACCOUNTING,NEW
YORK
20,RESEARCH,DALLAS
40,OPERATIONS,BOSTON
30,SALES,CHICAGO
50,HUMAN
RESOURCE,BOSTON
60,IT,BOSTON
70,PRODUCTION,DALLAS
80,QUALITY,BOSTON
Here is my control file
$ cat *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)
I am specifying the DISCARDMAX parameter
value as 1. This command will load the records in the flat file upto the second
discard record.
$ sqlldr sthomas/tiger
control=/home/oracle/st/sqlload.ctl DISCARDMAX=1
SQL*Loader: Release
11.2.0.3.0 - Production on Thu Jun 6 11:26:17 2013
Copyright (c) 1982,
2011, Oracle and/or its affiliates. All
rights reserved.
Commit point reached
- logical record count 5
Content for the discard file.
$ cat *dsc
40,OPERATIONS,BOSTON
50,HUMAN
RESOURCE,BOSTON
Here is the records loaded to the table
SQL> select *
from dept;
DEPTNO DNAME LOC
----------
-------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
_____________________________________________________________________________________________________________________
0 comments:
Post a Comment