有一个文件,名称是export.txt,该文件前面几行如下所示:
000000000800,SEP000000000800,800
000000000810,SEP000000000810,810
000000000813,000000000813,813
000000000820,SEP000000000820,820
其中第一列和第二列是VARCHAR2,第三列是INTEGER
我在scott下建立了一个表是phone,三个字段的名称分别是MAC, DESCRIPTIONS, EXTENTION
前两个字段类型为VARCHAR2(20), 第三个字段类型为INTEGER
我现在希望将txt文件导入到scott.phone表里
任意文本编辑器创建.ctl文件,内容如下:
INTO TABLE phone
FIELDS TERMINATED BY ","TRAILING NULLCOLSITPUB(MAC,DESCRIPTIONS,EXTENTION)
SQLLDR username/password@tns_name CONTROL=c:\temp\discount.ctl LOG=c:\temp\discount.logDATA=c:\temp\discount.txt BAD=c:\temp\discount.bad ERRORS=100000
如果没有使用 trailing nullcols,则当导入的数据中有字段为空值的情况下,会报如下错误:
export.log的内容如下所示,193条记录中有11条没有导入。
SQL*Loader: Release 10.2.0.1.0 - Production on Sat Jun 20 23:34:27Copyright (c) 1982, 2005, Oracle.All rights reserved.
Control File: Export.
Data File: export.txt
Bad File: export.bad
Discard File:none specified
(Allow all discards)
Number to load: ALL
Number to skip: 0
Errors allowed: 50
Continuation: none specified
Path used: Conventional