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 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?

Wedge

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

Gangboard

Thanks for the link, it is very useful! :) However, according to that documentation, the LOAD WHEN clause is not used to choose between one of several field layouts: "The LOAD WHEN condition_spec clause is used to identify the records that should be passed to the database. The evaluation method varies:" Do you happen to have an example of this clause choosing between different field layouts rather than choosing which records will be exported? – Wedge

You are going to end up with multiple external tables. One table for TYPE1 records and another for TYPE2 records. If you think about it a table can not have multiple layouts. – Gangboard

I was wondering if I would have to go that route. I understand that a table cannot have multiple layouts. However, in my case, all line types specify the same fields, just in different positions. Thanks for the tip! :) Would you like to edit your answer to include your latest comment? – Wedge

actually you have me curious now I know more. Going to "play" with this a bit later. – Gangboard

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 to then columns depending on record-type declare cursor c1 is

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

Penoyer