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.
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.
