External table adalah tabel yang datanya ada di luar database, biasanya berupa text file. External table sering digunakan untuk :
- membaca file dari database Oracle.
- Me-load (import) data dari text file ke database. Sebagai alternatif lain dari SQL*Loader.
Misalkan saya punya file
$ 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
bang kalo mo import semua file dmp, gmn ya langkah2nya… langsung k e-mail aja… makasih
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!
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……
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
^”