vee’s notes

this is about everything

SQL*Loader Oracle September 11, 2009

Filed under: oracle — veeolina @ 9:58 am
Tags: , ,

SQL Loader adalah tool Oracle yang dapat digunakan untuk me-load data dari flat file atau text file ke dalam database.

Misalnya terdapat file employee.csv yang isinya seperti berikut:

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

Selain itu juga terdapat file birthdate.txt yang isinya:

 19861103
 19550624
 19770723
 19481119

Data pada file employee.txt akan di-load ke tabel employee:

create table employee(
 empno    number, 
 ename    varchar2(30), 
 job    varchar2(20), 
 sal    number
);

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

create table birthdate (
 tahun    char(4), 
 bulan    char(2), 
 hari    char(2)
) 

Berikut langkah-langkah untuk me-load data dari text file ke database Oracle:
1. Buat control file yang berisi parameter untuk SQL*Loader
Berikut contoh control file untuk employee.csv:

 load data 
 replace into table employee 
 fields terminated by ";"
 (empno, ename, job, sal)

Data pada file employee.csv adalah contoh delimeted data (variable length). Sedangkan data pada file birthdate.txt adalah contoh positional data (fixed length). Control filenya dapat berbentuk seperti di bawah ini:

 load data 
 infile '/home/oracle/data/birthdate.txt' 
 append 
 into table birthdate 
 (   
   tahun    position(1:4),   
   bulan    position(5:6),   
   hari     position(7:8)
 )

2. Load data ke tabel employee dengan menggunakan perintah sqlldr:

 sqlldr USERID=oracle/oracle CONTROL=employee.ctl, DATA=employee.csv, LOG=employee.log, BAD=employee.bad
 sqlldr USERID=oracle/oracle CONTROL=birthdate.ctl, DATA=birthdate.txt, LOG=birthdate.log, BAD=birthdate.bad

sqlldr memiliki beberapa parameter, di antaranya seperti yang terlihat pada perintah di atas. Untuk mengetahui parameter apa saja yang tersedia, eksekusi perintah “sqlldr” tanpa argumen apapun. Parameter yang sering digunakan adalah:
* USERID: user dan password database Oracle
* CONTROL: control file yang digunakan untuk me-load data
* DATA: data (file) yang akan di-load
* LOG: file yang digunakan menyimpan log dari proses load
* BAD: file yang digunakan untuk menyimpan data yang tidak diproses

Data yang akan di-load ke Oracle juga dapat berada di dalam control file (in-line data). Caranya dengan menspesifikasikan nama data file-nya dengan “*” dan menambahkan keyword “begindata” untuk memulai data section. Contohnya sebagai berikut:

 load data 
 infile * 
 replace 
 into table birthdate 
 (   
   tahun    position(1:4),   
   bulan    position(5:6), 
   hari    position(7:8) 
 ) 
 begindata 
 19861103 
 19550624 
 19770723 
 19481119

SQL Loader juga memberikan kemudahan-kemudahan, di antaranya:
* Memungkinkan untuk memodifikasi data yang akan di-load ke database  terlebi dahulu

 load data 
 infile * 
 into table modifikasi_data 
 ( 
   empno        position(1:4),       
   deptcode    position(5:7)"upper(:deptcode)",   
   start_date    position(8:15)"to_date(:start_date,'YYYYMMDD')" 
 ) 
 begindata 
 0001HRD20070808 
 0002FIN20071223  
 0003FIN20080103

* Load data dari beberapa input file sekaligus

 load data 
 infile    file1.dat 
 infile    file2.dat 
 infile    file3.dat 
 append 
 into table employee 
 fields terminated by ";"
 (empno, ename, job, sal)

* Load data ke beberapa table sekaligus

load data 
infile * 
into table purchase when code like 'pur%' 
fields terminated by "," 
(   
  code    char(3),   
  expense number 
) 
into table sale when code='sal%' 
fields terminated by "," 
(   
  code    char(3),   
  income number 
) 
begindata 
sal0001,1000000 
pur0001,200000 
sal0002,500000 
sal0003,200000

* Load hanya data-data tertentu saja

 load data  
 infile *
 append
 into table my_table when (01)='D'
 ( 
   date    position(2:9),
   code    position(10:15)  
 ) 
 begindata
 D20090708AD56HJ
 D20090808DF89KH
 H20090808500000
 D20090908JK57OP

* Skip header dari data
Hal ini dapat dilakukan dengan menspesifikasikan “SKIP=n”. n menunjukkan jumlah row yang tidak akan di load ke database.

 sqlldr USERID=oracle/oracle CONTROL=employee.ctl, DATA=employee.txt, LOG=employee.log, BAD=employee.bad, skip=1

* Skip column tertentu agar tidak di-load ke database
Dapat dilakukan dengan menggunakan keyword “FILLER” atau “BOUNDFILLER”. BOUNDFILLER digunakan jika nilai suatu column tidak akan di-load ke database tapi untuk selanjutnya nilainya dibutuhkan.
Contoh1:

 load data 
 truncate
 into table table1
 fields terminated by ','
 (
   field1,   field2 FILLER,   field3
 )

Contoh2:

load data
infile *
truncate into table table2
fields terminated by ","  trailing nullcols 
(   
   column1,   
   field2 BOUNDFILLER,
   field3 BOUNDFILLER,
   field4 BOUNDFILLER,
   field5 BOUNDFILLER,
   column2     ":field2 || :field3",
   column3     ":field4 + :field5"
 )
 

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;