SQLLOAD举例

 

1.加载的数据,purprice.txt文件内容如下:
10014130|2010249|2012/10/18|RUI.LI|1100|7.80|CNY|0247101|深圳市商之杰贸易发展有限公司|6932850200481|420481
10000010|2010562|2012/10/18|RUI.LI|1100|3.00|CNY|0315101|*深圳市国免丽晶企业发展有限公司|6902227040941|129178
10000010|2010484|2012/10/18|RUI.LI|1100|3.00|CNY|1349092|(新)深圳市国免丽晶企业发展有限公司|6902227040941|129178
10000019|2010622|2012/10/18|RUI.LI|1100|14.20|CNY|1346520|*深圳市新华通实业有限公司|6914782202566|301074
10000019|2010586|2012/10/18|RUI.LI|1100|14.20|CNY|1349266|东莞徐记食品有限公司凤岗分公司|6914782202566|301074
10000019|2010587|2012/10/18|RUI.LI|1100|14.20|CNY|1350950|东莞徐记食品有限公司龙岗分公司|6914782202566|301074

2.数据库表结构如下:
create table TB_SAP_DATA
(
  ID1  VARCHAR2(128),
  ID2  VARCHAR2(128),
  ID3  VARCHAR2(128),
  ID4  VARCHAR2(128),
  ID5  VARCHAR2(128),
  ID6  VARCHAR2(128),
  ID7  VARCHAR2(128),
  ID8  VARCHAR2(128),
  ID9  VARCHAR2(128),
  ID10 VARCHAR2(128),
  ID11 VARCHAR2(128)
);

3.通过如下语句获取控制文件的字段部分
SELECT
decode (xh, 1, ' ', ' , ')||
rpad (column_name, 33, ' ')||
rpad('CHAR('|| data_length ||')', 16, ' ')||
'"trim(:'||
trim(column_name)||
')"'
FROM
(
select
RANK() OVER ( PARTITION BY table_name ORDER BY column_id ASC ) AS xh
,column_name
,data_length
from user_tab_columns
where table_name= upper ('tb_sap_data')
) A
ORDER BY A.xh;

4.创建控制文件test.ctl
控制文件test.ctl内容如下
load data
infile 'C:\purprice.txt'
truncate into table tb_sap_data
fields terminated by '|'
TRAILING NULLCOLS
(
  ID1                              CHAR(128)       "trim(:ID1)"
 ,ID2                              CHAR(128)       "trim(:ID2)"
 ,ID3                              CHAR(128)       "trim(:ID3)"
 ,ID4                              CHAR(128)       "trim(:ID4)"
 ,ID5                              CHAR(128)       "trim(:ID5)"
 ,ID6                              CHAR(128)       "trim(:ID6)"
 ,ID7                              CHAR(128)       "trim(:ID7)"
 ,ID8                              CHAR(128)       "trim(:ID8)"
 ,ID9                              CHAR(128)       "trim(:ID9)"
 ,ID10                             CHAR(128)       "trim(:ID10)"
 ,ID11                             CHAR(128)       "trim(:ID11)"
)

5.开始加载数据
sqlldr userid=hxl/hxl@hxl control=c:\test.ctl log=c:\test.lg bad=c:\test.bad

 

--The End--