External tables allow Oracle to
query data that is stored outside the database in flat files. The ORACLE_LOADER
driver can be used to access any data stored in any format that can be loaded
by SQL*Loader. No DML can be performed on external tables but they can be used
for query, join and sort operations. Views and synonyms can be created against
external tables. They are useful in the ETL process of data warehouses since
the data doesn't need to be staged and can be queried in parallel. They should
not be used for frequently queried tables.
1. Create the directory, MY_DATA
which will contain the data file.
CREATE OR REPLACE DIRECTORY my_data AS ‘D:\DATA_FILE’;
2. The data file, emp.csv
contains the employee number, employee name, job, manager number, salary,
commission and department number in the following format:
7369,SMITH,CLERK,7902,800,,20
7499,ALLEN,SALESMAN,7698,1600,300,30
7521,WARD,SALESMAN,7698,1250,500,30
7566,JONES,MANAGER,7839,2975,,20
7654,MARTIN,SALESMAN,7698,1250,1400,30
7698,BLAKE,MANAGER,7839,2850,,30
7782,CLARK,MANAGER,7839,2450,,10
7788,SCOTT,ANALYST,7566,3000,,20
7839,KING,PRESIDENT,,5000,,10
7844,TURNER,SALESMAN,7698,1500,0,30
7876,ADAMS,CLERK,7788,1100,,20
7900,JAMES,CLERK,7698,950,,30
7902,FORD,ANALYST,7566,3000,,20
7934,MILLER,CLERK,7782,1300,,10
3. Create the external table, emp_external.
CREATE TABLE
emp_external
(
EMPNO NUMBER(4),
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2)
)
ORGANIZATION EXTERNAL
(TYPE ORACLE_LOADER
DEFAULT DIRECTORY my_data
ACCESS PARAMETERS
(RECORDS DELIMITED BY NEWLINE
LOGFILE my_data:'data.log'
BADFILE my_data:'data.bad'
DISCARDFILE my_data:'data.disc'
fields terminated by ','
MISSING FIELD VALUES ARE NULL)
LOCATION (my_data:'emp.csv')
);
The DEFAULT DIRECTORY points to
the directory which contains the data file. LOGFILE, BADFILE and DISCARDFILE
are the files which will be created when the data is loaded into the database.
LOCATION indicates the name of the data file in the directory.
4. You can now access the data in
the file by querying the external table.
SQL> select * from emp_external;
Output:
 |
| Query output of External Table |
