Sqlloader简单用法

Sqlloader简单用法

SQL> create table t_load(a varchar2(10),b varchar2(10),c varchar2(10),d varchar2(10),e varchar2(10),f varchar2(10));
Table created
--数据文件
Data.txt:
"a","b","c","d","e","f"
"aaa","bb","c","d0","eee","f"
"baa","db","f","d1","eee","f"
"caa","eb","g","d2","eee","f"
"daa","fb","h","d3","eee","f"
--控制文件
Load.ctl:
LOAD DATA
INFILE 'g:\data.txt'
INTO TABLE T_LOAD
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(a,b,c,d,e,f)
--使用sqlldr进行加载
C:\Documents and Settings\linyuefeng>sqlldr gdlddw/gdlddw@dss control=G:\load.ct
l log=G:\load.log
SQL*Loader: Release 9.2.0.6.0 - Production on 星期四 9月 20 11:58:37 2007
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
达到提交点，逻辑记录计数5

SQL> select * from t_load;
A          B          C          D          E          F
---------- ---------- ---------- ---------- ---------- ----------
a          b          c          d          e          f
aaa        bb         c          d0         eee        f
baa        db         f          d1         eee        f
caa        eb         g          d2         eee        f
daa        fb         h          d3         eee        f

--如何将不想要的列去掉？
Load.ctl:
LOAD DATA
INFILE 'g:\data.txt'
INTO TABLE T_LOAD
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(a filler,
 b,
 c,
 d filler,
 e,
 f filler)

SQL> truncate table t_load;
Table truncated
C:\Documents and Settings\linyuefeng>sqlldr gdlddw/gdlddw@dss control=G:\load.ct
l log=G:\load.log
SQL*Loader: Release 9.2.0.6.0 - Production on 星期四 9月 20 13:40:22 2007
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
达到提交点，逻辑记录计数5
SQL> select * from t_load;
A          B          C          D          E          F
---------- ---------- ---------- ---------- ---------- ----------
           b          c                     e         
           bb         c                     eee       
           db         f                     eee       
           eb         g                     eee       
           fb         h                     eee
--使用ociuldr工具进行数据加载（ociuldr工具可以自动生成控制文件）
G:\>ociuldr user=gdlddw/gdlddw@dss query="select * from t_load" table="loader"
    5500 bytes allocated for column A (1)
    5500 bytes allocated for column B (2)
    5500 bytes allocated for column C (3)
    5500 bytes allocated for column D (4)
    5500 bytes allocated for column E (5)
    5500 bytes allocated for column F (6)

       0 rows exported at 2007-09-20 14:07:48
       5 rows exported at 2007-09-20 14:07:48
         output file uldrdata.txt closed at 5 rows.
上面table="loader"指定了控制文件的名字。自动生成的loader_sqlldr.ctl文件：
--
-- Generated by OCIULDR
--
OPTIONS(BINDSIZE=8388608,READSIZE=8388608,ERRORS=-1,ROWS=50000)
LOAD DATA
INFILE 'uldrdata.txt' "STR X'0a'"
INTO TABLE t_load –此处将表名修改为要加载的表名就行
FIELDS TERMINATED BY X'2c' TRAILING NULLCOLS
(
  A CHAR(10),
  B CHAR(10),
  C CHAR(10),
  D CHAR(10),
  E CHAR(10),
  F CHAR(10)
)
uldrdata.txt文件的内容：
,b,c,,e,
,bb,c,,eee,
,db,f,,eee,
,eb,g,,eee,
,fb,h,,eee,

SQL> truncate table t_load;
Table truncated

G:\>sqlldr gdlddw/gdlddw@dss control=G:\loader_sqlldr.ctl log=G:\loader_sqlldr.l
og
SQL*Loader: Release 9.2.0.6.0 - Production on 星期四 9月 20 14:10:04 2007
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
达到提交点，逻辑记录计数5
SQL> select * from t_load;
A          B          C          D          E          F
---------- ---------- ---------- ---------- ---------- ----------
           b          c                     e         
           bb         c                     eee       
           db         f                     eee       
           eb         g                     eee       
           fb         h                     eee

其它SQLLOADER的详细用法：
http://zhouwf0726./post/9689/157970
如何在SqlLoader中装载日期型的数据
http://www./archives/2005/07/eciousqlloadero.html