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 SQLLoader'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 SQLLoader 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?
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
If You have fixed records try this
Then You can access to then columns depending on record-type declare cursor c1 is
Hope this helps