Ada tool untuk melakukan sql unloader yaitu sqluldr2
1. download Binary di http://www.dbatools.net/software/ociuldr.zip
2. extract
[oracle@test ~]$ unzip ociuldr.zip
Archive: ociuldr.zip
inflating: sqluldr2_linux32_10204.bin
inflating: sqluldr2_linux64_10204.bin
inflating: sqluldr2.exe
inflating: sqluldr2_aix64_9208.bin
3. ubah mode-nya
[oracle@test ~]$ chmod a+x sqluldr2_linux64_10204.bin
4. test
[oracle@test ~]$ ./sqluldr2_linux64_10204.bin
SQL*UnLoader: Fast Oracle Text Unloader (GZIP, Parallel), Release 4.0.1
(@) Copyright Lou Fangxin (AnySQL.net) 2004 – 2010, all rights reserved.
Usage: SQLULDR2 keyword=value [,keyword=value,...]
Valid Keywords:
user = username/password@tnsname
sql = SQL file name
query = select statement
field = separator string between fields
record = separator string between records
rows = print progress for every given rows (default, 1000000)
file = output file name(default: uldrdata.txt)
log = log file name, prefix with + to append mode
fast = auto tuning the session level parameters(YES)
text = output type (MYSQL, CSV, MYSQLINS, ORACLEINS, FORM, SEARCH).
charset = character set name of the target database.
ncharset= national character set name of the target database.
parfile = read command option from parameter file
for field and record, you can use ’0x’ to specify hex character code,
\r=0x0d \n=0x0a |=0x7c ,=0x2c, \t=0×09, :=0x3a, #=0×23, “=0×22 ‘=0×27
5. rename
[oracle@test ~]$ mv sqluldr2_linux64_10204.bin SQLULDR2
6. DEMO 1 : file xls
[oracle@test ~]$ ./SQLULDR2 user=scott/TIGER query=”select * from dept” field=0×09 file=scott_dept.xls
0 rows exported at 2011-07-11 14:39:59, size 0 MB.
4 rows exported at 2011-07-11 14:39:59, size 0 MB.
output file scott_dept.xls closed at 4 rows, size 0 MB.
[oracle@test ~]$ cat scott_dept.xls
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
7. DEMO 2 : file cvs
[oracle@test ~]$ ./SQLULDR2 user=scott/TIGER query=”select * from dept” field=0x2c file=scott_dept.cvs
0 rows exported at 2011-07-11 14:42:39, size 0 MB.
4 rows exported at 2011-07-11 14:42:39, size 0 MB.
output file scott_dept.cvs closed at 4 rows, size 0 MB.
[oracle@test ~]$ cat scott_dept.cvs
10,ACCOUNTING,NEW YORK
20,RESEARCH,DALLAS
30,SALES,CHICAGO
40,OPERATIONS,BOSTON
8. DEMO 3 : column dengan line char (“|”).
[oracle@test ~]$ ./SQLULDR2 user=scott/TIGER query=”select * from dept” field=0x7c file=scott_dept.dat
0 rows exported at 2011-07-11 14:45:12, size 0 MB.
4 rows exported at 2011-07-11 14:45:12, size 0 MB.
output file scott_dept.dat closed at 4 rows, size 0 MB.
[oracle@test ~]$ cat scott_dept.dat
10|ACCOUNTING|NEW YORK
20|RESEARCH|DALLAS
30|SALES|CHICAGO
40|OPERATIONS|BOSTON
Tags: sql unloader, sqlloader, sqluldr2
setijoagus
