External Table - Discard File not generated as expected

Question

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 unlimited
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.

Anything of significance in your doc.log file? – DCookie Jun 17 '11 at 16:27 — DCookie, Jun 17 '11 at 16:27
What is different about those 4/5 records ? – Gary Myers Jun 18 '11 at 01:20 — Gary Myers, Jun 18 '11 at 01:20

score 0 · Answer 1 · answered Mar 08 '13 at 17:35

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

score 0 · Answer 2 · answered Dec 19 '14 at 07:26

0

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.

answered Dec 19 '14 at 07:26

Kakash1hatake

128
12

score -1 · Answer 3 · answered Oct 16 '20 at 19:28

-1

you can remove the asteric and bracket it should work, in the load when eor='EOR'

answered Oct 16 '20 at 19:28

S Nayak

1
1

External Table - Discard File not generated as expected

3 Answers3