Sunday, August 05, 2007

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

No comments: