oracle读取datatable bool栏位,如何使用sqlldr的WHEN判断加载数据到不同的数据表

如何使用sqlldr的WHEN判断加载数据到不同的数据表

D:\Orion>sqlplus eygle/eygle

SQL*Plus: Release 9.2.0.6.0 - Production on 星期日 7月 3 19:53:59 2005

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

连接到:

Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.6.0 - Production

19:53:59 SQL> create table test1 (

19:54:14 2 a1 varchar2(10),

19:54:21 3 a2 varchar2(10),

19:54:26 4 a3 varchar2(10));

表已创建。

已用时间: 00: 00: 00.03

19:54:32 SQL> create table test2 (

19:54:35 2 a1 varchar2(10),

19:54:39 3 a2 varchar2(10),

19:54:40 4 a3 varchar2(10));

表已创建。

已用时间: 00: 00: 00.04

19:54:42 SQL> exit

从Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.6.0 - Production中断开

测试数据:

D:\Orion>cat data.txt

01,KunMing,YunNan

02,BeiJing,BeiJing

02,ShenZhe,ShenZhe

02,TianJin,TianJin

D:\Orion>

控制文件:

D:\Orion>cat data.ctl

LOAD DATA

INFILE 'data.txt'

APPEND INTO TABLE test1

WHEN (2) = '1'

FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY " " TRAILING NULLCOLS

( a1 POSITION(01:02), a2 POSITION(04:10), a3 POSITION(12:19) )

INTO TABLE test2

WHEN (2) = '2'

FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY " " TRAILING NULLCOLS

( a1 POSITION(01:02), a2 POSITION(04:10), a3 POSITION(12:19) )

加载数据:

D:\Orion>sqlldr eygle/eygle errors=20000 log=data.log control=data.ctl

SQL*Loader: Release 9.2.0.6.0 - Production on 星期日 7月 3 19:57:36 2005

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

达到提交点，逻辑记录计数3

达到提交点，逻辑记录计数4

检查结果:

D:\Orion>sqlplus eygle/eygle

SQL*Plus: Release 9.2.0.6.0 - Production on 星期日 7月 3 19:57:38 2005

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

连接到:

Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.6.0 - Production

19:57:38 SQL> select * from test1;

A1 A2 A3

---------- ---------- ----------

01 KunMing YunNan

已用时间: 00: 00: 00.00

19:57:42 SQL> select * from test2;

A1 A2 A3

---------- ---------- ----------

02 BeiJing BeiJing

02 ShenZhe ShenZhe

02 TianJin TianJin

已用时间: 00: 00: 00.00

19:57:45 SQL> exit

从Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.6.0 - Production中断开

如果不选择分区表，可以使用此方式直接加载数据到不同的数据表中。

By eygle on 2005-07-03 19:33 |

Comments (1) |

Backup&Recovery | 328 |

1 Comment

要是数据文件长短不一

01,KunMing,YunNan

02,BeiJisdng,BeiJing

02,ShenZdhe,ShenZhe

02,TianfffJin,TianJin

就不行了！