Oracle External Table

Suppose we have a text file named “emp.data”. This text files contains employees data in following format,

1, Michle , clerk
2, king , manager
3, joe , clerk

What if we want to use this data in SQL query? Remember we do not want to import this data in oracle database but we want to reference this data from its physical location. To achieve this oracle database has provided a feature called External Tables. Using this feature, we can register “emp.dat” file in oracle database as external table.

In oracle database, an external table resides outside of database. These tables can be used in SQL queries like regular database tables. Views can also be created on external tables but no DML operation can be performed on external table. This is why external tables are read-only. Also we cannot create indexes on external tables.

External tales are created using ORGANIZATION EXTERNAL clause in CREATE TABLE statement. Following section creates an external table to reference data in “emp.dat” data.

Create a directory as c:\external_data. Create a new file as “emp.dat” in c:\external_data directory. Copy and paste following three rows in “emp.dat” file,


1, Michle , clerk
2, king , manager
3, joe , clerk

Now Connect to oracle database from sys user and issue following SQL statements,

create or replace directory data_dir as 'C:\external_table';
grant read,write on directory data_dir to scott;

Now connect to SCOTT user and following CREATE TABLE statement to create external table


CREATE TABLE EXT_TABLE
(EMP_ID NUMBER,
EMP_NAME VARCHAR2(50),
EMP_JOB VARCHAR2(20))
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY DATA_DIR
ACCESS PARAMETERS
(
records delimited by newline
fields terminated by ','
)
LOCATION ('EMP.DAT')
)

Now we can query external table. We can use this external table like we use other database tables.

All above process is shown in figure below.

External Tables
External Tables

For further details you may visit following link

http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/tables.htm#sthref2445

2 thoughts on “Oracle External Table”

Leave a Reply

Your email address will not be published. Required fields are marked *