Menggunakan External Table

External table adalah tabel yang datanya ada di luar database, biasanya berupa text file. External table sering digunakan untuk :

1. membaca file dari database Oracle.
2. Me-load (import) data dari text file ke database. Sebagai alternatif lain dari SQL*Loader.

Misalkan saya punya file penjualan. dat di direktori /data1/oracle/Users/rohmad/external.

$ cd /data1/oracle/Users/rohmad/external
$ more penjualan.dat
3286;23-DEC-08;SEMARANG
3287;24-DEC-08;SURABAYA
3288;25-DEC-08;MAKASAR
3289;26-DEC-08;MEDAN
3290;26-DEC-08;MAGELANG TENGAH

Berikut ini langkah-langkah untuk membuat external table berdasarkan file tersebut.

Persiapan
Buat directory di database yang mengarah ke directory file tersebut.

SQL> conn / as sysdba
SQL> CREATE OR REPLACE DIRECTORY external_dir 
AS '/data1/oracle/Users/rohmad/external'; 

Beri privilege ke user agar bisa membaca dan menulis ke directory tersebut.

SQL> GRANT READ ON DIRECTORY external_dir TO test; 
SQL> GRANT WRITE ON DIRECTORY external_dir TO test; 

Membuat External Table

SQL> conn test
SQL> CREATE TABLE ext_penjualan
    ( no_invoice 	NUMBER,
      tgl_jual    	DATE,
      area 		varchar2(10))
ORGANIZATION EXTERNAL 
     ( 
       TYPE ORACLE_LOADER 
       DEFAULT DIRECTORY external_dir
       ACCESS PARAMETERS 
       ( 
         records delimited by newline 
         badfile external_dir:'penjualan.bad' 
         logfile external_dir:'penjualan.log' 
         fields terminated by ';' 
         missing field values are null 
         ( no_invoice, tgl_jual, area
         ) 
       ) 
       LOCATION ('penjualan.dat') 
     ) 
     REJECT LIMIT UNLIMITED; 

Query External Table

SQL> select * from ext_penjualan;

NO_INVOICE TGL_JUAL  AREA
---------- --------- ----------
      3286 23-DEC-08 SEMARANG
      3287 24-DEC-08 SURABAYA
      3288 25-DEC-08 MAKASAR
      3289 26-DEC-08 MEDAN

Dari kelima records yang ada di file penjualan.dat, ada 4 records yang terbaca oleh Oracle. Sekarang kita lihat log-nya, pasti ada error ketika membaca salah satu record tersebut.

$ cd /data1/oracle/Users/rohmad/external
$ more penjualan.log
error processing column AREA in row 5 for datafile /data1/oracle/Users/rohmad/external/penjualan.dat
ORA-12899: value too large for column AREA (actual: 15, maximum: 10)

Errornya sama persis dengan yang pernah saya bahas di Menggunakan SQL*Loader. Sebagaimana SQL*Loader, record yang tidak diproses ditaruh di BADFILE (penjualan.bad).

Kalau yang salah adalah datanya, misalnya kolomnya kepanjangan, ya datanya yang mesti diedit. Kalau datanya tidak masalah, itu artinya kolom di tabel yang kurang panjang. Kita bisa mengedit external tabel tersebut:

SQL> alter table ext_penjualan modify (area varchar2(15));
SQL> select * from ext_penjualan;

NO_INVOICE TGL_JUAL  AREA
---------- --------- ---------------
      3286 23-DEC-08 SEMARANG
      3287 24-DEC-08 SURABAYA
      3288 25-DEC-08 MAKASAR
      3289 26-DEC-08 MEDAN
      3290 26-DEC-08 MAGELANG TENGAH

Load data dengan external table
Nah, sekarang kita bisa me-load (import) data dari file penjualan.dat kedatabase dengan memanfaatkan external table.

Misalkan data akan diload ke tabel history_penjualan:

CREATE TABLE history_penjualan
    ( no_invoice 	NUMBER,
      tgl_jual    	DATE,
      area 		varchar2(15));

Ya, tinggal insert saja:

SQL> insert into history_penjualan select * from ext_penjualan;
SQL> select * from history_penjualan;

NO_INVOICE TGL_JUAL  AREA
---------- --------- ---------------
      3286 23-DEC-08 SEMARANG
      3287 24-DEC-08 SURABAYA
      3288 25-DEC-08 MAKASAR
      3289 26-DEC-08 MEDAN
      3290 26-DEC-08 MAGELANG TENGAH

Referensi
Oracle® Database Administrator’s Guide 10g Release 2 (10.2) – Managing External Table