Oracle External Tables: Advanced Flat File Layout

Question

I wish to create an external table in an Oracle database, retrieving its data from a flat file on the server. The format of this file is non-trivial. Each line in this file can be one of several different layouts, depending on the line's prefix (the prefix itself is always a fixed length). For example, a line beginning with 'TYPE1' would have a different layout than a line beginning with 'TYPE2'.

I have read that external tables can take advantage of all the constructs made available to SQL*Loader's control files. However, any documentation I have read only seams to deal with trivial flat-file layouts whereby all lines share a common layout. A SQL*Loader control file could easily handle this scenario using the WHEN clause:

WHEN (1:5) = 'TYPE1'
(
    field1 POSITION(10:18),
    field2 POSITION(26:35)
)
WHEN (1:5) = 'TYPE2'
(
    field1 POSITION(23:27),
    field2 POSITION(15:19)
)

How can I express such a layout using Oracle's external table definition syntax?

Answer 1

This is from 9.2 docs but you need the LOAD WHEN clause.

http://download.oracle.com/docs/cd/B10500_01/server.920/a96652/ch12.htm