vee’s notes

this is about everything

External Table Oracle September 7, 2009

Filed under: oracle — veeolina @ 3:25 pm
Tags: , ,

External table adalah tabel yang datanya ada di luar database, biasanya berupa flat file atau text file.
External table biasanya digunakan sebagai alternatif pengganti SQL*Loader untuk me-load data dari flat file ke database.

Misalnya terdapat dua file employees.csv dan birthdate.txt yang akan di-load ke database.
File employee.csv terdiri dari 4 records yang field-fieldnya dipisahkan oleh tanda baca koma:

7369,Smith, Clerck,800
7499,Allen,Salesman,1600
7521,Ward,Salesman,1250
7566,Jones,Manager,2975

File birthdate.txt terdiri dari 4 records yang panjang field-fieldnya sudah fixed:

19861103
19550624
19770723
19481119

Berikut langkah-langkah untuk membuat external table dari kedua file tersebut:
1. Buat direktori yang mengarah ke direktori di mana file tersebut disimpan

create or replace directory external_dir as '/home/oracle/data';

2. Beri privilege ke user agar bisa membaca dan menulis ke direktori tersebut

grant read, write on directory external_dir to oracle;

3. Buat external table

Untuk file employees.csv yang field-field nya dipisahkan oleh koma, external table-nya adalah:

create table ext_employee (
 empno    number,
 ename    varchar2(30),
 job      varchar2(20),
 sal      number        
)
organization external (
 type    oracle_loader
 default directory external_dir
 access parameters (
   records delimited  by newline
   badfile external_dir:'employee.bad'
   logfile external_dir:'employee.log'
   fields  terminated by ','
   missing field values are null
   (
     empno, ename, job, sal        
   )
 )
 location ('employee.csv')
)
reject limit unlimited;

Sedangkan untuk file birthdate.txt yang panjang setiap field nya tetap, external table nya adalah sebagai berikut:


create table ext_birthdate (
 tahun    char(4), 
 bulan    char(2), 
 hari    char(2)
)
organization external ( 
 type    oracle_loader 
 default directory external_dir  
 access parameters (   
   records delimited by newline
   badfile external_dir:'birthdate.bad'
   logfile external_dir:'birthdate.log'   
   fields(     
     tahun    position(1:4) char(4), 
     bulan    position(5:6) char(2), 
     hari    position(7:8) char(2)   
   ) 
 )
 location ('birthdate.txt')
)
reject limit unlimited;

Masing-masing external table tersebut dapat di query:

select * from ext_employee;
select * from ext_birthdate;

Selanjutnya external table yang telah dibuat dapat digunakan untuk me-load data ke database.
Misalnya, data pada file employee.csv akan di-load ke table employee:

insert into employee select * from ext_employee;

Sedangkan, data pada file birtdate.txt akan di load ke table birthdate:

insert into birthdate select * from ext_birthdate;
Advertisements
 

4 Responses to “External Table Oracle”

  1. Tania Says:

    veeolina.wordpress.com to GoogleReader!
    Tania

  2. Zoran Says:

    Thanks for article. Everytime like to read you.
    Have a nice day


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s