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'