External Table - Discard File not generated as expected
I have an external table with the below definition.
create table app_doctor (
doc_no number(9),
age number(3),
eor char(3 char)
)
organization external
( type oracle_loader
default directory "DOC"
access parameters
(
records delimited by newline
**load when (eor = "EOR")**
string sizes are in characters
logfile 'doc.log'
badfile 'doc.bad'
discardfile 'doc.dsc'
fields
(
doc_no position (1:9) integer external(9),
age position (10:12) integer external(3),
eor position (13:15) char(3)
)
)
location('HOSP_DOC.txt')
)
reject limit unli开发者_开发知识库mited
parallel 5;
I feed 100 records, of which 50 are valid, 20 are invalid, and 30 do not satisfy the WHEN condition, but I see only 4 or 5 records in my discard file.
Using the following external table definition:
create table app_doctor (
doc_no number(9),
age number(3),
eor char(3 char)
)
organization external
( type oracle_loader
default directory "DOC"
access parameters
(
records delimited by newline
**load when (eor = "EOR")**
string sizes are in characters
logfile 'doc.log'
badfile 'doc.bad'
discardfile 'doc.dsc'
fields
(
doc_no position (1:9) integer external(9),
age position (10:12) integer external(3),
eor position (13:15) char(3)
)
)
location('HOSP_DOC.txt')
)
reject limit unlimited
parallel 5;
And the following test data file:
123456789021EOR 123456789021EOR 123456789021EOR 123456789021EOR 123456789021FER 123456789021FER 123456789021FER asdfasfa9021ABC asdfasfa9021ABC asdfasfa9021ABCasdfasdas 123456789021ABC 123456789021FER 123456789021EOR 123456789021EOR 123456789021EOR 123456789021ABC 123456789021ABC 123456789021ABC
I get 7 valid records returned when running
select * from app_doctor;
My discard file contains all 11 bad records and the log file doc.log
contains the correct information about what happened during execution:
LOG file opened at 03/08/13 11:33:56 Field Definitions for table APP_DOCTOR Record format DELIMITED BY NEWLINE Data in file has same endianness as the platform Rows with all null fields are accepted Load when (EOR = EOR) Fields in Data Source: DOC_NO Integer external (9) Record position (1, 9) Trim whitespace same as SQL Loader AGE Integer external (3) Record position (10, 12) Trim whitespace same as SQL Loader EOR CHAR (3) Record position (13, 15) Trim whitespace same as SQL Loader LOG file opened at 03/08/13 11:33:56 Field Definitions for table APP_DOCTOR Record format DELIMITED BY NEWLINE Data in file has same endianness as the platform Rows with all null fields are accepted Load when (EOR = EOR) Fields in Data Source: DOC_NO Integer external (9) Record position (1, 9) Trim whitespace same as SQL Loader AGE Integer external (3) Record position (10, 12) Trim whitespace same as SQL Loader EOR CHAR (3) Record position (13, 15) Trim whitespace same as SQL Loader KUP-04102: record 5 discarded from file /apps/oracle/doc/HOSP_DOC.txt KUP-04102: record 6 discarded from file /apps/oracle/doc/HOSP_DOC.txt KUP-04102: record 7 discarded from file /apps/oracle/doc/HOSP_DOC.txt KUP-04102: record 8 discarded from file /apps/oracle/doc/HOSP_DOC.txt KUP-04102: record 9 discarded from file /apps/oracle/doc/HOSP_DOC.txt KUP-04102: record 10 discarded from file /apps/oracle/doc/HOSP_DOC.txt KUP-04102: record 11 discarded from file /apps/oracle/doc/HOSP_DOC.txt KUP-04102: record 12 discarded from file /apps/oracle/doc/HOSP_DOC.txt KUP-04102: record 16 discarded from file /apps/oracle/doc/HOSP_DOC.txt KUP-04102: record 17 discarded from file /apps/oracle/doc/HOSP_DOC.txt KUP-04102: record 18 discarded from file /apps/oracle/doc/HOSP_DOC.txt
according to the oracle documentation
The DISCARDFILE clause names the file to which records are written that fail the condition in the LOAD WHEN clause.
you can remove the asteric and bracket it should work, in the load when eor='EOR'
精彩评论