General Development Discussions

1. Typo in MISSING FILED VALUES ARE NULL

2. RECORDS DELIMITED BY NEWLINE must preceed FIELDS TERMINATED BY '|'

3. There is no such SQL*Loader/External table field type VARCHAR2 (in this case you don't even need to specify it)

SQL> CREATE TABLE emp_load
  2       (employee_number      VARCHAR2(50),
  3        employee_last_name   VARCHAR2(50),
  4         employee_first_name  VARCHAR2(50),
  5        employee_middle_name VARCHAR2(50),
  6         employee_hire_date   VARCHAR2(50))
  7      ORGANIZATION EXTERNAL
  8        (TYPE ORACLE_LOADER
  9        DEFAULT DIRECTORY TEMP
10        ACCESS PARAMETERS (
11          RECORDS DELIMITED BY NEWLINE
12          FIELDS TERMINATED BY '|'
13          MISSING FIELD VALUES ARE NULL
14           (employee_number,
15        employee_last_name,
16         employee_first_name,
17        employee_middle_name,
18         employee_hire_date)
19         )
20        LOCATION ('info.dat')
21       ) REJECT LIMIT UNLIMITED
22  /

Table created.

SQL> SELECT  *
  2    FROM  emp_load
  3  /

EMPLOYEE_N EMPLOYEE_L EMPLOYEE_F EMPLOYEE_M EMPLOYEE_HI
---------- ---------- ---------- ---------- -----------
010        ABC        DEF        XYZ        03-DEC-2011

SQL>

SY