How to use a flat file as an Oracle External Table

In this example, we define an External Table that corresponds to a fixed-length flat file.

A much better description can be found here.

The file must be present in an Oracle Directory.

The file is mapped to a table with a command similar to the following:

CREATE TABLE my_external_table
(
   field1    VARCHAR2(8)    ,
   field2    VARCHAR2(8)    ,
   field3    VARCHAR2(6) ,
   .....
   fieldn   VARCHAR2(40)
)
ORGANIZATION EXTERNAL 
   (
   TYPE oracle_loader
   DEFAULT DIRECTORY my_external_dir
   ACCESS PARAMETERS
      ( RECORDS DELIMITED BY NEWLINE
        FIELDS
        (
        FIELD1     POSITION(1:8) ,
        FIELD2     POSITION(9:16),
        FIELD3     POSITION(17:22),
        .....
        FIELDN    POSITION(1457:1496)
       )
     )
     LOCATION ('my_flat_file.dat')
  )
REJECT LIMIT UNLIMITED;

Once the table has been created, then it is possible to perform queries as with any normal table:

select *
from my_external_table
where rownum < 10;

Note that the ACCESS PARAMETERS section can contain any valid sql*loader statement.