Oracle External Tables

Oracle External tables are not like regular table. We can apply DML statement on regular table. But in external table, you can not apply DML statement except SELECT. We can not create index on external table. In oracle9i, read only operations are permitted, but in oracle10g, we can also write out data to an external table, although you can't write to an existing table.

Oracle uses SQL*Loader functionality, through the ORACLE_LOADER access driver to move data from the flat file into the database; it uses a Data Pump access driver to move data out of the DB into a file in an Oracle-proprietary format, and back into the database from files of that format.

This article is written in oracle10g on windows.

Steps to create an External table

Step 1.

Create the directory on server as well as database.

C:\>mkdir ext
C:\>cd ext
C:\ext>

SQL> connect sys as sysdba
Enter password: ********
Connected.
SQL> create directory extdir
2 as 'c:/ext';

Directory created.

SQL> grant read,write on directory extdir to scott;

Grant succeeded.

SQL>

Step2

Place the text file on directory c:/ext location and create the external table as below.

Here is the text file content.

7369,SMITH,CLERK,20
7499,ALLEN,SALESMAN,30
7521,WARD,SALESMAN,30
7566,JONES,MANAGER,20
7654,MARTIN,SALESMAN,30
7698,BLAKE,MANAGER,30
7782,CLARK,MANAGER,10
7788,SCOTT,ANALYST,20
7839,KING,PRESIDENT,10
7844,TURNER,SALESMAN,30
7876,ADAMS,CLERK,20
7900,JAMES,CLERK,30
7902,FORD,ANALYST,20
7934,MILLER,CLERK,10

SQL> show user
USER is "SCOTT"
SQL> create table emptable
2 (empno number,
3 empname varchar2(50),
4 job varchar2(50),
5 deptno number)
6 organization external
7 ( default directory extdir
8 access parameters
9 ( records delimited by newline
10 fields terminated by ',')
11 location ('emp.txt'));

Table created.
SQL>
SQL> select empno,substr(empno,1,10),substr(job,1,10),deptno
2 from emptable;

EMPNO SUBSTR(EMP SUBSTR(JOB DEPTNO
---------- ---------- ---------- ----------
7369 7369 CLERK 20
7499 7499 SALESMAN 30
7521 7521 SALESMAN 30
7566 7566 MANAGER 20
7654 7654 SALESMAN 30
7698 7698 MANAGER 30
7782 7782 MANAGER 10
7788 7788 ANALYST 20
7839 7839 PRESIDENT 10
7844 7844 SALESMAN 30
7876 7876 CLERK 20
7900 7900 CLERK 30
7902 7902 ANALYST 20
7934 7934 CLERK 10

14 rows selected.

SQL>

We could add log file, bad file, discard file for the external table.

SQL> alter table emptable
2 access parameters
3 ( records delimited by newline
4 badfile extdir:'emp.bad'
5 logfile extdir:'emp.log'
6 discardfile extdir:'emp.dsc'
7 fields terminated by ','
8 ) ;

Table altered.

SQL>

Loading data from external table to Regular table

We can load the data from external table to oracle table as below. We can do the same job through SQLLOADER. So, now the question is, when do we use the sqlloader and when do we use the external table for loading data from flat file to oracle table. External table has good filtering capability while loading data from external table to oracle table.

In the below example, we are loading data only for deptno 10 and 20. For this situation, it is worth to try external table... If we want to load only specific set of data from flat file, external table is good option.

SQL> create table empload_db as select * from
2 emptable where deptno in(10,20);

Table created.

SQL> select empno,substr(empno,1,10),substr(job,1,10),deptno
2 from empload_db;

EMPNO SUBSTR(EMP SUBSTR(JOB DEPTNO
---------- ---------- ---------- ----------
7369 7369 CLERK 20
7566 7566 MANAGER 20
7782 7782 MANAGER 10
7788 7788 ANALYST 20
7839 7839 PRESIDENT 10
7876 7876 CLERK 20
7902 7902 ANALYST 20
7934 7934 CLERK 10

8 rows selected.

SQL>

Loading data from regular table to external table.

This feature is introduced in oracle10g. We shoud use oracle_datapump driver to load data into external table.

SQL> create table load_ext
2 organization external
3 ( type oracle_datapump
4 default directory extdir
5 location ('emp.dmp')
6 ) as select * from emptable
7 /

Table created.

SQL>
SQL> select empno,substr(empno,1,10),substr(job,1,10),deptno
2 from load_ext;

EMPNO SUBSTR(EMP SUBSTR(JOB DEPTNO
---------- ---------- ---------- ----------
7369 7369 CLERK 20
7499 7499 SALESMAN 30
7521 7521 SALESMAN 30
7566 7566 MANAGER 20
7654 7654 SALESMAN 30
7698 7698 MANAGER 30
7782 7782 MANAGER 10
7788 7788 ANALYST 20
7839 7839 PRESIDENT 10
7844 7844 SALESMAN 30
7876 7876 CLERK 20
7900 7900 CLERK 30
7902 7902 ANALYST 20
7934 7934 CLERK 10

14 rows selected.

SQL>

You can now move the file you just created, emp.dmp, to another system and create an external table to read the data:

SQL> create table import_load_ext
2 (empno number,
3 empname varchar2(50),
4 job varchar2(50),
5 deptno number)
6 organization external
7 ( type oracle_datapump
8 default directory extdir
9 location ('emp.dmp')
10 );

Table created.

SQL> set linesize 100
SQL> select empno,substr(empno,1,10),substr(job,1,10),deptno
2 from import_load_ext;

EMPNO SUBSTR(EMP SUBSTR(JOB DEPTNO
---------- ---------- ---------- ----------
7369 7369 CLERK 20
7499 7499 SALESMAN 30
7521 7521 SALESMAN 30
7566 7566 MANAGER 20
7654 7654 SALESMAN 30
7698 7698 MANAGER 30
7782 7782 MANAGER 10
7788 7788 ANALYST 20
7839 7839 PRESIDENT 10
7844 7844 SALESMAN 30
7876 7876 CLERK 20
7900 7900 CLERK 30
7902 7902 ANALYST 20
7934 7934 CLERK 10

14 rows selected.

Restriction on external tables :

1. An external table does not allow INSERT, UPDATE, DELETE statement. We get ORA-30657: operation not supported on external organized table error when apply DML statement except SELECT.

2. An external table does not describe how data is stored in the external source.

3. An external table cannot load data into a LONG column.

4. Column name or table names are specified in the external table access parameters, certain values are considered to be reserved words by the access parameter parser. If a reserved word is used as an identifier, it must be enclosed in double quotation marks.


If you want to read more on external tables... Please read here..

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6611962171229