External Table

1. Create a directory

Login as SYS

In Windows:
create or replace directory ext_dir as 'E:\Denis_Files\ORACLE\Practise_Oracle\External_Table'

grant read, write on directory ext_dir to SCOTT;

2. Test with cormma dilimited file

create table ext_table_csv (
i Number,
n Varchar2(20),
m Varchar2(20)
)
organization external (
type oracle_loader
default directory ext_dir
access parameters (
records delimited by newline
fields terminated by ','
missing field values are null
)
location ('tab_corma.csv')
)
reject limit unlimited;

scott@ORCL> select * from ext_table_csv;

I N M
---------- -------------------- --------------------
1 scott tiger
2 denis sun
3 fuxia jin
4 eric sun
5 david wang
6 jack paul


3. Test with fixed field length

create table ext_table_fixed (
field_1 char(4),
field_2 char(30)
)
organization external (
type oracle_loader
default directory ext_dir
access parameters (
records delimited by newline
fields (
field_1 position(1:4) char( 4),
field_2 position(5:30) char(30)
)
)
location ('tab_fixed.csv')
)
reject limit unlimited;


scott@ORCL> select * from ext_table_fixed;
FIEL FIELD_2
---- ------------------------------
b001 xyz
b002 denis
b003 alex


Note:
======
During testing encountered the following errors, after make the directory in which the flat files are saved rwx to all (chmod 777), problem solved

scott@ORCL> select * from ext_table_csv;
select * from ext_table_csv
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04063: unable to open log file EXT_TABLE_CSV_256_2996.log
OS error The data is invalid.
ORA-06512: at "SYS.ORACLE_LOADER", line 19



To use external table to do parallel direct-path load:
-------------------------------------------------------
a. create external_table
b. create table T nologging parallel as select * from external_table
ref: Tom Kyte, Effective Oracle by Design, p193