Oracle External Tables: Extended Flat File Layout

Question

I want to create an external table in an Oracle database by extracting 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 prefix (the prefix itself is always a fixed length). For example, a line starting with 'TYPE1' will have a different layout than a line starting with 'TYPE2' .

I read that external tables can take advantage of all the constructs available for SQLLoader control files. However, in any documentation that I read only seams, you are dealing with trivial flat file formats in which all lines have a common layout. The SQLLoader control file can easily handle this scenario with 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 the syntax for defining an external Oracle table?

Answer 1

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

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

Answer 2

If you have fixed records try this

 create table EXT_TABLE ( record_type char(2), customer_id char(10), customer_name char(60), item_id char(12) quantity char(10) ) organization external ( type ORACLE_LOADER default directory DIR_FLUX_DEV access parameters ( RECORDS DELIMITED BY NEWLINE BADFILE 'ext_table.bad' LOGFILE 'ext_table.log' SKIP 0 FIELDS ( TP_REC position(1:2) char(2), customer_id position(3:10) char(10), customer_name position(13:60) char(60), item_id position(3:12) char(12), quantity position(15:10) char(10) ) ) location (DIR_FLUX_DEV:'file.txt') ) reject limit 0; 

Then you can access the columns depending on the type of record to declare cursor c1 -

 select e.* from ext_table; begin for r in c1 loop if r.tp_rec = '02' then dbms_output.put_line(r.tp_rec || ' ' || r.customer_id); elsif r.tp_rec = '03' then dbms_output.put_line(r.tp_rec || ' ' || r.item_id); end if; end loop; end; 

Hope this helps