ORA-29913, ORA-29400, KUP-00554 while querying external table

Problem Description
While querying an external table it fails with error ORA-29913, ORA-29400, KUP-00554, KUP-01005 like below.

SQL> create directory ext_dir as 'c:\';

Directory created.

SQL> create table external_table(
  2  col1 varchar2(1),
  3  col2 varchar2(20),
  4  col3 varchar2(10)
  5  )
  6  organization external
  7  (type oracle_loader
  8  default directory ext_dir
  9  access parameters
 10  (
 11  records delimited by newline
 12  fields
 13  missing field value are null
 14  (col1 position(1,1),
 15  col2 position(2,20),
 16  col3 position(21,30)
 17  )
 18  )
 19  location ('c:\temp\TEST.TXT')
 20  )
 21  ;

Table created.

SQL> select * from external_table;
select * from external_table
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-00554: error encountered while parsing access parameters
KUP-01005: syntax error: found "identifier": expecting one of: "values"
KUP-01008: the bad identifier was: value
KUP-01007: at line 3 column 15

Cause of the Problem
While creating external table the access parameters are not parsed. The access parameters are parsed when the external table is queried. The above errors are returned due to syntax error in the external table access parameters.

Solution of the Problem
The solution is correct the syntax in the external table creation access parameters. Let's try to solve syntax error one by one.

- Drop the table as in the database two tables as same name under one schema can't exist.

SQL> drop table external_table;

Table dropped.

- Write the previous external table creation script.

SQL> create table external_table(
  2  col1 varchar2(1),
  3  col2 varchar2(20),
  4  col3 varchar2(10)
  5  )
  6  organization external
  7  (type oracle_loader
  8  default directory ext_dir
  9  access parameters
 10  (
 11  records delimited by newline
 12  fields
 13  missing field value are null
 14  (col1 position(1,1),
 15  col2 position(2,20),
 16  col3 position(21,30)
 17  )
 18  )
 19  location ('c:\temp\TEST.TXT')
 20  )
 21

- At line 13 the keyword will be values.

SQL> 13
 13* missing field value are null
SQL> c/value/values
 13* missing field values are null

- At line 14, 15 and 16 position value comma (,) will be replaced by colon (:).

SQL> 14
 14* (col1 position(1,1),
SQL> c/1,1/1:1
 14* (col1 position(1:1),
SQL> 15
 15* col2 position(2,20),
SQL> c/2,20/2:20
 15* col2 position(2:20),
SQL> 16
 16* col3 position(21,30)
SQL> c/21,30/21:30
 16* col3 position(21:30)
SQL> /

Table created.

- Now selecting the table avoid any syntax type errors but it lead to another error related to path. In the external table we can't specify absolute path in this way. So in the following example we are correcting that.

SQL> select * from external_table;
select * from external_table
              *
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04076: file name cannot contain a path specification: c:\temp\TEST.TXT

SQL> drop table external_table;

Table dropped.

- Creating external table after correcting path.

SQL> create table external_table(
  2  col1 varchar2(1),
  3  col2 varchar2(20),
  4  col3 varchar2(10)
  5  )
  6  organization external
  7  (type oracle_loader
  8  default directory ext_dir
  9  access parameters
 10  (
 11  records delimited by newline
 12  fields
 13  missing field values are null
 14  (col1 position(1:1),
 15  col2 position(2:20),
 16  col3 position(21:30)
 17  )
 18  )
 19  location ('test.txt')
 20  );

Table created.

SQL> select * from external_table;

C COL2                 COL3
- -------------------- ----------
1  222222222222222222  2 11111111
1  222255555555555222  2 11111333