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

This entry was posted in Administration, Utilities and tagged , , , , , . Bookmark the permalink.

4 Responses to Menggunakan External Table

  1. fe says:

    bang kalo mo import semua file dmp, gmn ya langkah2nya… langsung k e-mail aja… makasih

  2. runa sudadi says:

    Thanks mas, ini sangat membantu krn saya emang berkeinginan mengload data text file ke table oracle, pertanyaannya:
    1. gimana jika text file saya tidak dipisahkan ; contoh
    NOMORNAMAALAMATTARIFTAGIHAN
    NOMORNAMAALAMATTARIFTAGIHAN
    NOMORNAMAALAMATTARIFTAGIHAN
    2. saya juga berkeinginan mengload data dbf (tentu ada colum headernya) ke table oracle, apakah caranya sama?
    3. pada contoh kasus 1. bisakah saya cuma load beberapa colum aja, misal nama alamat?
    Tks atas pencerahannya!

  3. wildan says:

    salam kenal,
    bagi yang sudah dapat file PDF Kitab Suci Oracle, saya mohon kirim ke email saya wildaninf2002@yahoo.com
    masalahnya saya sudah coba, tapi gak bisa-bisa
    Terima Kasih……

  4. yana says:

    salam kenal
    saya sudah mencoba untuk export dari *.dat menggunakan sql loader tetapi masih error, apa ya maslahnya
    mohon bantuannya

    “SQL*Loader-350: Syntax error at line 1.
    Expecting keyword LOAD, found “ÿþG”.
    ÿþG
    ^”

Leave a Reply

Your email address will not be published. Required fields are marked *