Oracle for Beginner: Create External Table

In Oracle 10g, there is a data pump module that handle almost moving data from database Oracle to another database or external storage. Except data pump, there is a oracle loader that can be used to create and access an external data file (csv) as a regular table. In this article we will try to create an external table with data pump and oracle loader. This is a very simple thing that you can do easily in Oracle 10g.

First, we will try to create external table with Oracle data pump module :

$ sqlplus /nolog
SQL> conn / as sysdba
SQL> create directory mylab as 'your/data/path';

SQL> create table dept(department_name)
2  organization external
3  (
4    type ORACLE_DATAPUMP
5    DEFAULT DIRECTORY mylab
6    LOCATION ('ware.exp')
7  )
8  AS select distinct department_name from hr.departments;

SQL> select * from dept;

Next, in same manner, we can access an external csv as a regular table in Oracle database using Oracle Loader:

• Create a csv file. For example we will create kota.dat with the following values:

Jakarta,DKI
Surabaya,Jawa Timur
Semarang,Jawa Tengah
Yogyakarta,DIY
Malang,Jawa Timur
Bandung,Jawa Barat



• login to your database as sysdba, create a directory object as a map for your data directory, and create an external table:
  

$ sqlplus /nolog
SQL> conn / as sysdba
SQL> create directory mydata as 'your/data/path';

SQL> create table kota(kota varchar(50), propinsi varchar(50))
 2  organization external
 3  (type oracle_loader default directory mydata
 4   access parameters
 5  ( records delimited by newline
 6    badfile mydata:'kota%a_%p.bad'
 7    logfile mydata:'kota%a_%p.log'
 8    fields terminated by ','
 9    missing field values are null
10    (kota, propinsi)
11  )
12  location ('kota.dat') );

SQL> select * from kota;

I hope you can find a new experience with Oracle Database.