create table employdetails1(
name varchar2(14),firstname char(21),lastname char(21),phone number(12),email varchar2(14),
dept number(4)
)
ORGANIZATION EXTERNAL
(
type oracle_loader
default directory test_dir
  access paramEters(
    records delimited by newline
    fields terminated by ','
        missing field values are null
        (
        name varchar2(14),firstname char(21),lastname char(21),phone number(12),email varchar2(14),
dept number(4)
        )
    )    
  LOCATION ('EMPLOYDETAILS.TXT')
  )
  
  reject limit unlimited;
  

SQL>
SQL> SHOW USER
USER is "SYS"
SQL> PASSW SYS
Changing password for SYS
Old password:
New password:
Retype new password:
ERROR:
ORA-01005: null password given; logon denied

SCOTT@orcl> HOST TYPE c:\my_oracle_files\employdetails.txt
001,Hutt,Jabba,896743856,jabba@thecompany.com,18
002,Simpson,Homer,382947382,homer@thecompany.com,20
003,Kent,Clark,082736194,superman@thecompany.com,5
004,Kid,Billy,928743627,billythkid@thecompany.com,9
005,Stranger,Perfect,389209831,nobody@thecompany.com,23
006,Zoidberg,Dr,094510283,crustacean@thecompany.com,1

SCOTT@orcl> CREATE OR REPLACE DIRECTORY test_dir AS 'c:\my_oracle_files'
  2  /

Directory created.

SCOTT@orcl> CREATE TABLE employdetails1
  2    (name       VARCHAR2(14),
  3     firstname  CHAR(21),
  4     lastname   CHAR(21),
  5     phone      NUMBER(12),
  6     email      VARCHAR2(25),
  7     dept       NUMBER(4))
  8  ORGANIZATION EXTERNAL
  9    (TYPE oracle_loader
 10     DEFAULT DIRECTORY test_dir
 11     ACCESS PARAMETERS
 12       (RECORDS DELIMITED BY NEWLINE
 13        LOGFILE 'TEST_DIR':'test.log'
 14        FIELDS TERMINATED BY ','
 15        MISSING FIELD VALUES ARE NULL
 16          (name       CHAR(14),
 17           lastname   CHAR(21),
 18           firstname  CHAR(21),
 19           phone      INTEGER EXTERNAL(12),
 20           email      CHAR(25),
 21           dept       INTEGER EXTERNAL(12)))
 22     LOCATION ('employdetails.txt'))
 23  REJECT LIMIT UNLIMITED
 24  /

Table created.

SCOTT@orcl> SELECT * FROM employdetails1
  2  /

NAME           FIRSTNAME             LASTNAME                   PHONE EMAIL                           DEPT
-------------- --------------------- --------------------- ---------- ------------------------- ----------
001            Jabba                 Hutt                   896743856 jabba@thecompany.com              18
002            Homer                 Simpson                382947382 homer@thecompany.com              20
003            Clark                 Kent                    82736194 superman@thecompany.com            5
004            Billy                 Kid                    928743627 billythkid@thecompany.com          9
005            Perfect               Stranger               389209831 nobody@thecompany.com             23
006            Dr                    Zoidberg                94510283 crustacean@thecompany.com          1

6 rows selected.

CREATE TABLE employdetails1
  (name       VARCHAR2(14),
   firstname  CHAR(21),
   lastname   CHAR(21),
   phone      NUMBER(12),
   email      VARCHAR2(25),
   dept       NUMBER(4))
ORGANIZATION EXTERNAL
  (TYPE oracle_loader
   DEFAULT DIRECTORY test_dir
   ACCESS PARAMETERS
     (RECORDS DELIMITED BY NEWLINE
      LOGFILE 'TEST_DIR':'test.log'
      FIELDS TERMINATED BY ','
      MISSING FIELD VALUES ARE NULL
        (name       CHAR(14),  
         lastname   CHAR(21),
         firstname  CHAR(21),
         phone      INTEGER EXTERNAL(12),
         email      CHAR(25),
         dept       INTEGER EXTERNAL(12)))
   LOCATION ('employdetails.txt'))
REJECT LIMIT UNLIMITED;