What are External Tables? -
Contrary to what their name indicates, "external tables" are not really the tables. They are only the objects in the form of a definition stored in the data dictionary (catalog). They are capable of providing the data in the normal table format while the data is actually stored in operating system files "external" to the database (hence the name "external tables"). So dropping of the external tables only removes its definition from the data dictionary while data still remains safe in the files. These tables allow all sorts of operations like normal tables do - queries, joins, and DML (from Oracle 10g onwards). So external tables are objects in the form of a definition which do not store any data in themselves but only enable you to view externally stored data as if it were inside a table in the database. The trick is actually the data fields in the external file are merely "mapped" to the external table columns, not actually loaded into them.
External tables are said to imitate functionality of a bulk data load utility called "SQL*Loader" and is considered to be equivalent to storing "SQL*Loader Control File" in the form of a definition of table and hence the same plain SQL (and also PL/SQL, a server side programming language in Oracle) is also allowed to be used against the External Tables. However data transformation and filtering capabilities are considered to be much more flexible than SQL*Loader.
A working example of External Tables -
Step 1 - creating a directory object
A directory object is object defined in Oracle database to point to a file system location where our source flat files are stored. It is the way of protecting the source files from unauthorized access as there are also privileges associated with the directory objects which are granted from the database with "grant" command -
create directory extdir as 'c:\oracle\dfile'; -- directory creation
grant read, write on directory extdir to scott; --read & write privilege granted
Step 2 - Creating external table
create table empxt (
default directory extdir
records delimited by newline
fields terminated by ','
missing field values are null
reject limit unlimited;
Explanation : The part of definition above "organization external" is plain create table with definitions of colulmns, "organization external" clause tells oracle that the table is to be created as external table, "type" clause specifies the engine - oracle_loader is used for loading data from file to the internal table, "default directory" points to the directory where all of our source data files and rejection & logfiles will be housed, "location" provides the name of the input data file.
Step 3 - the input data file (external file)
The input data files may contain the records in two formats - (1) fields delimited by a character for example comma as is used here (2) or fields with fixed width format [in which case the access parameters of this example will change accordingly]. For this example the data may in the following way -
Step 4 - we can now query the external table by simple SQL
select * from empxt;
Step 5 - We can populate our EMP table (internal)
insert into EMP select * from empxt;
This was a very simple example but it is possible to perform a lot of transformation operations, use oracle's built-in functions, in-line formulas etc. for a more exiting run of the bulk data load operation.
The most important of the advantages of using the external tables is that you can use the common SQL to query the data from external tables which mask the source data files with its structure and almost any operation that we normally do in query may be performed in this query too against the external tables.