External Tables Example From Oracle Documentation

test3>create directory ext_tab_dir as '/user1/oracle/test_vinay';

Directory created.

test3>grant read,write on directory ext_tab_dir to osvinay;

Grant succeeded.

test3>conn osvinay@test3
Enter password: *********
Connected.
test3>CREATE TABLE emp_load (first_name CHAR(15), last_name CHAR(20), year_of_birth CHAR(4))
  2    ORGANIZATION EXTERNAL (TYPE ORACLE_LOADER DEFAULT DIRECTORY ext_tab_dir
  3                           ACCESS PARAMETERS (RECORDS FIXED 20 FIELDS (first_name CHAR(7),
  4                                                                      last_name CHAR(8),
  5                                                                      year_of_birth CHAR(4)))
  6                           LOCATION ('info.dat'));

Table created.

test3>
test3>select * from emp_load;

FIRST_NAME      LAST_NAME            YEAR
--------------- -------------------- ----
Alvin           Tolliver             1976
Kenneth         Baer                 1963

test3>drop table emp_load;

Table dropped.

test3>CREATE TABLE emp_load (first_name CHAR(15), last_name CHAR(20), year_of_birth CHAR(4))
  2    ORGANIZATION EXTERNAL (TYPE ORACLE_LOADER DEFAULT DIRECTORY ext_tab_dir
  3                           ACCESS PARAMETERS (RECORDS VARIABLE 2 FIELDS TERMINATED BY ','
  4                                               (first_name CHAR(7),
  5                                                last_name CHAR(8),
  6                                                year_of_birth CHAR(4)))
  7                           LOCATION ('info.dat'));

Table created.

test3>select * from emp_load;

FIRST_NAME      LAST_NAME            YEAR
--------------- -------------------- ----
Alvin           Tolliver             1976
Kenneth         Baer                 1963

test3>CREATE TABLE emp_load (first_name CHAR(15), last_name CHAR(20), year_of_birth CHAR(4))
  2   ORGANIZATION EXTERNAL (TYPE ORACLE_LOADER DEFAULT DIRECTORY ext_tab_dir
  3                          ACCESS PARAMETERS (RECORDS DELIMITED BY '|' FIELDS TERMINATED BY ','
  4                                               (first_name CHAR(7),
  5                                                last_name CHAR(8),
  6                                                year_of_birth CHAR(4)))
  7                          LOCATION ('info.dat'));

Table created.

test3>select * from emp_load;
select * from emp_load
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-30653: reject limit reached
ORA-06512: at "SYS.ORACLE_LOADER", line 52

I got this error when i followed the example from Oracle documentation. After several attempts i finally figured out that it only works if you change the values in info.dat file as follows:

bash-2.05$ more info.dat
Alvin,Tolliver,1976,|Kenneth,Baer,1963,|Mary,Dube,1973,

test3>/

FIRST_NAME      LAST_NAME            YEAR
--------------- -------------------- ----
Alvin           Tolliver             1976
Kenneth         Baer                 1963
Mary            Dube                 1973