Hi Tom,

Why do the records that do not have value for FLG get rejected?

Cheers,
Ganesh

DROP TABLE LNK_NUMRECS CASCADE CONSTRAINTS;

CREATE TABLE LNK_NUMRECS
(
TNAME VARCHAR2(30 CHAR),
NUMRECS NUMBER,
FLG VARCHAR2(1 CHAR)
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY TABLES_DIR
ACCESS PARAMETERS
( RECORDS DELIMITED BY NEWLINE
BADFILE tables_dir:'numrecs.bad'
LOGFILE tables_dir:'numrecs.log'
SKIP 1
FIELDS TERMINATED BY '<FS>'
LRTRIM
(TNAME,
NUMRECS,
FLG))
LOCATION (TABLES_DIR:'numrecs.txt')
)
REJECT LIMIT UNLIMITED;


numrecs.txt
TNAME<FS>NUMRECS<FS>FLG
ABSENCE_EMPLOYEE<FS>530<FS>Y
APPOINTEE_BENEFIT<FS>1906<FS>N
APPOINTEE_COSTING<FS>683
APPOINTMENTS<FS>8988
BENEFITS<FS>31
COSTING<FS>41
GRADE_SALARY_MODEL<FS>138
GRADES<FS>75
JOB_TITLE<FS>407
PERSON<FS>1147



numrecs.log
LOG file opened at 05/11/09 16:21:25

Field Definitions for table LNK_NUMRECS
Record format DELIMITED BY NEWLINE
Data in file has same endianness as the platform
Rows with all null fields are accepted

Fields in Data Source:

TNAME CHAR (255)
Terminated by "<FS>"
Trim whitespace from left and right
NUMRECS CHAR (255)
Terminated by "<FS>"
Trim whitespace from left and right
FLG CHAR (255)
Terminated by "<FS>"
Trim whitespace from left and right
KUP-04021: field formatting error for field FLG
KUP-04023: field start is after end of record
KUP-04101: record 4 rejected in file /u02/oradata/extracts/numrecs.txt
KUP-04021: field formatting error for field FLG
KUP-04023: field start is after end of record
KUP-04101: record 5 rejected in file /u02/oradata/extracts/numrecs.txt
KUP-04021: field formatting error for field FLG
KUP-04023: field start is after end of record
.
.
.

ops$tkyte%ORA10GR2> CREATE TABLE LNK_NUMRECS
  2  (
  3  TNAME  VARCHAR2(30 CHAR),
  4  NUMRECS NUMBER,
  5  FLG  VARCHAR2(1 CHAR)
  6  )
  7  ORGANIZATION EXTERNAL
  8  ( TYPE ORACLE_LOADER
  9    DEFAULT DIRECTORY MY_DIR
 10    ACCESS PARAMETERS
 11      ( RECORDS DELIMITED BY NEWLINE
 12    BADFILE 'numrecs.bad'
 13    LOGFILE 'numrecs.log'
 14    SKIP 1
 15    FIELDS TERMINATED BY '<FS>'
 16    LRTRIM<b>
 17        MISSING FIELD VALUES ARE NULL</b>
 18  (TNAME,
 19  NUMRECS,
 20  FLG))
 21    LOCATION ('numrecs.txt')
 22  )
 23  REJECT LIMIT UNLIMITED;

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select * from lnk_numrecs;

TNAME                             NUMRECS F
------------------------------ ---------- -
ABSENCE_EMPLOYEE                      530 Y
APPOINTEE_BENEFIT                    1906 N
APPOINTEE_COSTING                     683
APPOINTMENTS                         8988
BENEFITS                               31
COSTING                                41
GRADE_SALARY_MODEL                    138
GRADES                                 75
JOB_TITLE                             407
PERSON                               1147

10 rows selected.