This
article gives a brief understanding about External tables. External Tables are
defined as tables that do not reside in the database, and can be in any format
for which an access driver is provided. This external table definition can be
thought of as a view that allows running any SQL query against external data
without requiring that the external data first be loaded into the database.
You
can, for example, select, join, or sort external table data. You can also
create views and synonyms for external tables. However, no DML operations (UPDATE, INSERT, or
DELETE)
are possible, and indexes cannot be created on external tables.
Oracle
provides the means of defining the metadata for external tables through the CREATE TABLE ...
ORGANIZATION EXTERNAL statement.
Before firing the above command
we need to create a directory object where the external files will reside.
CREATE OR REPLACE DIRECTORY EXT_TABLES AS 'C:\EXT_TABLES\';
Example: The
example below describes how to create external files, create external tables,
query external tables and create views.
Step I: Creating the flat files, which will be queried
The
file "emp_ext1.dat"
contains the following sample data:
101,Andy,FINANCE,15-DEC-1995
102,Jack,HRD,01-MAY-1996
103,Rob,DEVELOPMENT,01-JUN-1996
104,Joe,DEVELOPMENT,01-JUN-1996
The
file "emp_ext2.dat"
contains the following sample data:
105,Maggie,FINANCE,15-DEC-1997
106,Russell,HRD,01-MAY-1998
107,Katie,DEVELOPMENT,01-JUN-1998
108,Jay,DEVELOPMENT,01-JUN-1998
Copy
these files under "C:\EXT_TABLES"
Step
II: Create a Directory Object where the
flat files will reside
SQL> CREATE OR REPLACE DIRECTORY EXT_TABLES AS 'C:\EXT_TABLES';
Directory created.
Step III: Create metadata for the
external table
SQL> CREATE TABLE emp_ext
(
empcode NUMBER(4),
empname VARCHAR2(25),
deptname VARCHAR2(25),
hiredate date
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY ext_tables
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY ','
MISSING FIELD VALUES ARE NULL
)
LOCATION ('emp_ext1.dat','emp_ext2.dat')
)
REJECT LIMIT UNLIMITED;
Table created.
The
REJECT LIMIT
clause specifies that there is no limit on the number of errors that can occur
during a query of the external data.
"The ORACLE_LOADER is an access driver for loading
data from the external files into the tables."
Step IV: Querying Data
SQL> SELECT * FROM emp_ext;
EMPCODE EMPNAME DEPTNAME HIREDATE
--------- ------------------- ---------------------- ---------
101 Andy FINANCE 15-DEC-95
102 Jack HRD 01-MAY-96
103 Rob DEVELOPMENT 01-JUN-96
104 Joe DEVELOPMENT 01-JUN-96
105 Maggie FINANCE 15-DEC-97
106 Russell HRD 01-MAY-98
107 Katie DEVELOPMENT 01-JUN-98
108 Jay DEVELOPMENT 01-JUN-98
8 rows selected.
Step V: Creating Views
SQL> CREATE VIEW v_empext_dev AS
SELECT * FROM emp_ext
WHERE deptname='DEVELOPMENT';
View created.
SQL> SELECT * FROM v_empext_dev;
EMPCODE EMPNAME DEPTNAME HIREDATE
------------ ------------- ---------------------- ---------
103 Rob DEVELOPMENT 01-JUN-96
104 Joe DEVELOPMENT 01-JUN-96
107 Katie DEVELOPMENT 01-JUN-98
108 Jay DEVELOPMENT 01-JUN-98
You
can get the information of the objects you have created through DBA_OBJECTS, ALL_OBJECTS
or USER_OBJECTS.
SQL> SELECT OBJECT_NAME, OBJECT_TYPE FROM ALL_OBJECTS
WHERE OBJECT_NAME LIKE 'EMP_EXT';
OBJECT_NAME OBJECT_TYPE
---------------------- ------------------
EMP_EXT TABLE
1 row selected.
SQL> SELECT OBJECT_NAME, OBJECT_TYPE FROM ALL_OBJECTS
WHERE OBJECT_NAME LIKE 'EXT_TABLES';
OBJECT_NAME OBJECT_TYPE
---------------------- ------------------
EXT_TABLES DIRECTORY
1 row selected.
Populating Tables using the INSERT command
You can populate data from external files using an
"insert into select from" statement instead of using SQL*Loader. This
method provides very fast data loads.
Example:
Consider a table EMPLOYEES:
SQL> desc EMPLOYEES;
Name Null? Type
--------------------------------- -------- --------------
EMPCODE NUMBER(4)
EMPNAME VARCHAR2(25)
DEPTNAME VARCHAR2(25)
HIREDATE DATE
SQL> INSERT INTO employees
(empcode,empname,deptname,hiredate) SELECT * FROM emp_ext;
8 rows created.
SQL> SELECT * FROM employees;
EMPCODE EMPNAME DEPTNAME HIREDATE
------------ ------------------- ---------------------- ---------
101 Andy FINANCE 15-DEC-95
102 Jack HRD 01-MAY-96
103 Rob DEVELOPMENT 01-JUN-96
104 Joe DEVELOPMENT 01-JUN-96
105 Maggie FINANCE 15-DEC-97
106 Russell HRD 01-MAY-98
107 Katie DEVELOPMENT 01-JUN-98
108 Jay DEVELOPMENT 01-JUN-98
8 rows selected.
Dropping External Tables
For
an external table, the DROP
TABLE statement removes only the table metadata in the database.
It has no affect on the actual data, which resides outside of the database.
Summary
The external files are thus tables in the data dictionary,
which can be queried as you would query ordinary Oracle tables. You can perform
fast data loads using the above method instead of using SQL*Loader.