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