sql unloader = sqluldr2

15 Jul

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=0x09, :=0x3a, #=0x23, “=0x22 ‘=0x27

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=0x09 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

One Response to “sql unloader = sqluldr2”

  1. Deepak Kumar Ojha December 20, 2012 at 5:19 am #

    This a nice utility. Is there any alternative to this utility?

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

%d bloggers like this: