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"
 )
Advertisements
 

4 Responses to “SQL*Loader Oracle”

  1. RobD Says:

    I usually don’t post on Blogs but ya forced me to, great info.. excellent! … I’ll add a backlink and bookmark your site. 🙂

  2. I don’t usually reply to posts but I will in this case, great info…I will add a backlink and bookmark your site. Keep up the good work!


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