External tables in Oracle
External tables allow Oracle to query data that is stored outside the database in flat files. The ORACLE_LOADER driver can be used to access any data stored in any format that can be loaded by SQL*Loader
No DML can be performed on external tables but they can be used for query, join and sort operations.
External tables can read flat files (that follow some rules) as though they were ordinary (although read-only) Oracle tables. Therefore, it is convinient to use external tables to load flat files into the DB.
The following tow examples show how to 'import' a file with externals table.
The first file consists of 4 records whose fields (attributes) are seperated by commas:
1,one,abc
2,two,def
3,three,ghi
4,four,jkl
The second file consists of 4 records whose first field is a fixed length field of 4 characters.
B000sekhar
B001anuradha
B002vanaja
B00CCharles
In order to reference these files, a directory must be created.
create or replace directory ext_dir as '/home/rene/ext_dir';
Of course, /home/rene/ext_dir must point to the directory where the file actually resides.
In order to prevent a ORA-29913, someone must grant read and write on the directory to the user that uses the directory:
grant read, write on directory ext_dir to cbyna;
It should be self explanatory that you cannot (in most cases) grant this privilege to yourself.
Comma delimited
Now, we're ready to create the table. We assume that the file name is file.csv.
create table ext_table_csv (
empno Number,
ename Varchar2(20),
job 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 ('file.csv')
)
reject limit unlimited;
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 ('file')
)
reject limit unlimited;
It's now possible to select from ext_table:
select * from ext_table;
In the directory specified with ext_dir, a log file will as well be written upon selecting from the external table.
Oracle 11g Release 2 introduced the PREPROCESSOR clause to identify a directory object and script used to process the files before they are read by the external table. This feature was backported to 11gR1 (11.1.0.7). The PREPROCESSOR clause is especially useful for reading compressed files, since they are unzipped and piped straight into the external table process without ever having to be unzipped on the file system.
CREATE OR REPLACE DIRECTORY exec_dir AS '/bin';
CREATE TABLE emp_ext (
emp_code VARCHAR2(5),
emp_name VARCHAR2(50),
language VARCHAR2(50)
)
ORGANIZATION EXTERNAL (
TYPE ORACLE_LOADER
DEFAULT DIRECTORY ext_tab_data
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
PREPROCESSOR exec_dir:'zcat'
FIELDS TERMINATED BY ','
MISSING FIELD VALUES ARE NULL
(
emp_code CHAR(5),
emp_name CHAR(50),
language CHAR(50)
)
)
LOCATION ('Countries1.txt.gz','Countries2.txt.gz')
)
PARALLEL 5
REJECT LIMIT UNLIMITED;
