Oracle External Table

Whit this feature you can access data in external sources as if it were a table in the database.
Prior to release 10g external tables were read-only (9i); from release 10g external tables can also be
written too.
To use the external tables feature you must have knowledge of the file format and record format of the datafiles on your platform if the ORACLE_LOADER access driver is used and the “source” is in text format.
When you create an external table you must specify :
o TYPE: ORACLE_LOADER (default). You can run only for loading data and the data must come from a file of text data. Loading from external tables to internal tables from the table is done by reading data files only external text.
o TYPE: ORACLE_DATAPUMP can perform both loads and unloads. The data must come from binary dump files. Uploads in internal tables from external tables are done by fetching from the binary dump files. Unloads from internal tables to external tables are done by populating the external tables binary file.
To do before create an external table
From a sqlplus session:
Sqlplus / as sysdba
1) CREATE DIRECTORY external_tab_dir AS ‘/usr/my/files’;
2) GRANT READ ON DIRECTORY external_tab_dir TO username;
Create an external table :
SQL> CREATE TABLE table_name
(column1 CHAR(5),
Column2 CHAR(20),
Column3 CHAR(15),
Column4 DATE)
ORGANIZATION EXTERNAL
( PARAMETERS
(RECORDS DELIMITED BY NEWLINE
FIELDS (field_col1 CHAR(2),
field_col2 CHAR(18),
field_col3 CHAR(11),
field_col4 CHAR(10) date_format DATE mask mm/dd/yyyy”
)
)
LOCATION (‘source.dat’)
);
Table created.