VMCD.ORG » SQL*Loader to load data with virtual column

在使用sqlldr工具导入数据的时候往往需要将导入的某列按顺序排列,我们可以使用sqlldr 的虚拟列功能实现

[oracle@liu ~]$ sqlplus ‘/as sysdba’

SQL*Plus: Release 10.2.0.1.0 – Production on Tue Dec 6 11:00:55 2011

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options

SQL> create table coke_coupon_record
(id int,
VERIFICATION_CODE varchar(20),
HAS_SEND number(8));

Table created.

cat coke_coupon_record.ctl
load data
characterset ZHS16GBK
infile ‘/home/oracle/tmp_coke_coupon_record.txt’
append into table coke_coupon_record
fields terminated by WHITESPACE
TRAILING NULLCOLS
(
“ID” sequence(MAX,1), ——MAX means Oracle will auto find the maxvalue of this column and will increase by 1
“VERIFICATION_CODE” ,
“HAS_SEND” “0”
)

cat tmp_coke_coupon_record.txt

FS39F6Z
8GP4G3T
N9VN8SP
3NY7FHS
C9RX39Q
6F79QBT
CZLTU4P
PG4KVG2
N55USUD
LV6VT5W
K6UBQY5
AV95KEK
LGA4AGU
GZWP648
HLRK7ZA
H58TG4E
DCR37L3
TCN5R59
GTUXEN5
FLGSL35
5BSQV2W
4XTGVFQ
WQCEQFN
B2MHSGD
FPKWCVY
2QUACHR
M9UZ4ZA
SBKHTSQ
RMEXDZC
BXT82CT
DQQ33S9
LN2EDPY
EASAYR4
F6FKM92
LLZQWKW
KG58R8X
7S7F9X5
E4CW8DS
BM558W2
B36MD2K
GSGWQ3C
2XF8EG2
ZEW8ZP9
MG6ND7L
FQ7V73G
RLYGNXU
NCWDQUD
YUW6X22
GKA6PXY
7HFZ9Y5
QF6EUDV
FKLLHQM
36WUYDU
PZ5TBE2
3T6DBFU
RALXBRA
GSQG8BN
GE7863V
KAZGRUZ
DLF8RFD
2XR26DW
BZA4WHX
ZUZM8XA
EEHY88Z
6ZCD3AG
X7GWUKP
GHG9YXR
TU32QCB
W33G23G
K2HA339
94K27UY
F9AASRP
TPMVPK7
YEE9M2Y
2QPL6C7
ZDVRTQT
DK3EWAD
2TR3S8Z
N9ZHC3E
W4XNXGC
HCUDDLA
YS2V25W
GMG9ZRT

[oracle@liu ~]$ sqlldr \’/ as sysdba\’ control=coke_coupon_record.ctl log=ceshi.log

SQL*Loader: Release 10.2.0.1.0 – Production on Tue Dec 6 11:49:05 2011

Commit point reached – logical record count 64
Commit point reached – logical record count 83
[oracle@liu ~]$ sqlplus ‘/as sysdba’

SQL*Plus: Release 10.2.0.1.0 – Production on Tue Dec 6 11:49:15 2011

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options

SQL> select * from coke_coupon_record;

ID VERIFICATION_CODE HAS_SEND
———- ——————– ———-
1 FS39F6Z 0
2 8GP4G3T 0
3 N9VN8SP 0
4 3NY7FHS 0
5 C9RX39Q 0
6 6F79QBT 0
7 CZLTU4P 0
8 PG4KVG2 0
9 N55USUD 0
10 LV6VT5W 0
11 K6UBQY5 0

ID VERIFICATION_CODE HAS_SEND
———- ——————– ———-
12 AV95KEK 0
13 LGA4AGU 0
14 GZWP648 0
15 HLRK7ZA 0
16 H58TG4E 0
17 DCR37L3 0
18 TCN5R59 0
19 GTUXEN5 0
20 FLGSL35 0
21 5BSQV2W 0
22 4XTGVFQ 0

ID VERIFICATION_CODE HAS_SEND
———- ——————– ———-
23 WQCEQFN 0
24 B2MHSGD 0
25 FPKWCVY 0
26 2QUACHR 0
27 M9UZ4ZA 0
28 SBKHTSQ 0
29 RMEXDZC 0
30 BXT82CT 0
31 DQQ33S9 0
32 LN2EDPY 0
33 EASAYR4 0

ID VERIFICATION_CODE HAS_SEND
———- ——————– ———-
34 F6FKM92 0
35 LLZQWKW 0
36 KG58R8X 0
37 7S7F9X5 0
38 E4CW8DS 0
39 BM558W2 0
40 B36MD2K 0
41 GSGWQ3C 0
42 2XF8EG2 0
43 ZEW8ZP9 0
44 MG6ND7L 0

ID VERIFICATION_CODE HAS_SEND
———- ——————– ———-
45 FQ7V73G 0
46 RLYGNXU 0
47 NCWDQUD 0
48 YUW6X22 0
49 GKA6PXY 0
50 7HFZ9Y5 0
51 QF6EUDV 0
52 FKLLHQM 0
53 36WUYDU 0
54 PZ5TBE2 0
55 3T6DBFU 0

ID VERIFICATION_CODE HAS_SEND
———- ——————– ———-
56 RALXBRA 0
57 GSQG8BN 0
58 GE7863V 0
59 KAZGRUZ 0
60 DLF8RFD 0
61 2XR26DW 0
62 BZA4WHX 0
63 ZUZM8XA 0
64 EEHY88Z 0
65 6ZCD3AG 0
66 X7GWUKP 0

ID VERIFICATION_CODE HAS_SEND
———- ——————– ———-
67 GHG9YXR 0
68 TU32QCB 0
69 W33G23G 0
70 K2HA339 0
71 94K27UY 0
72 F9AASRP 0
73 TPMVPK7 0
74 YEE9M2Y 0
75 2QPL6C7 0
76 ZDVRTQT 0
77 DK3EWAD 0

ID VERIFICATION_CODE HAS_SEND
———- ——————– ———-
78 2TR3S8Z 0
79 N9ZHC3E 0
80 W4XNXGC 0
81 HCUDDLA 0
82 YS2V25W 0
83 GMG9ZRT 0

83 rows selected.

同样我们可以调用sequence实现

[oracle@liu ~]$ cat coke_coupon_record.ctl

load data
characterset ZHS16GBK
infile ‘/home/oracle/tmp_coke_coupon_record.txt’
append into table coke_coupon_record
fields terminated by WHITESPACE
TRAILING NULLCOLS
(VERIFICATION_CODE,
HAS_SEND “0”,
ID “seq_coke_coupon_record.nextval” ——注意sequence 列需要放在最后
)

SQL>create sequence seq_coke_coupon_record start with 1 increment by 1 maxvalue 99999999 minvalue 1 cache 20;

Sequence created

SQL> select * from coke_coupon_record;

ID VERIFICATION_CODE HAS_SEND
———- ——————– ———-
78 2TR3S8Z 0
79 N9ZHC3E 0
80 W4XNXGC 0
81 HCUDDLA 0
82 YS2V25W 0
83 GMG9ZRT 0

83 rows selected.