sql load的CTL文件

有一个文件，名称是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文件，内容如下：
LOAD DATA
INFILE export.txt
INTO TABLE phone
FIELDS TERMINATED BY "," TRAILING NULLCOLS
(MAC,DESCRIPTIONS,EXTENTION)

 

SQLLDR   username/password@tns_name   CONTROL=c:\temp\discount.ctl   LOG=c:\temp\discount.log  DATA=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:27 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Control File:   Export.CTL
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
Bind array:     64 rows, maximum of 256000 bytes
Continuation:    none specified
Path used:      Conventional

Table PHONE, loaded from every logical record.
Insert option in effect for this table: INSERT

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
MAC                                 FIRST     *   ,       CHARACTER            
DESCRIPTIONS                         NEXT     *   ,       CHARACTER            
EXTENTION                            NEXT     *   ,       CHARACTER            

Record 41: Rejected - Error on table PHONE, column EXTENTION.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 149: Rejected - Error on table PHONE, column EXTENTION.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 169: Rejected - Error on table PHONE, column EXTENTION.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 170: Rejected - Error on table PHONE, column EXTENTION.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 177: Rejected - Error on table PHONE, column EXTENTION.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 179: Rejected - Error on table PHONE, column EXTENTION.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 185: Rejected - Error on table PHONE, column EXTENTION.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 186: Rejected - Error on table PHONE, column EXTENTION.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 187: Rejected - Error on table PHONE, column EXTENTION.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 189: Rejected - Error on table PHONE, column EXTENTION.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 190: Rejected - Error on table PHONE, column EXTENTION.
Column not found before end of logical record (use TRAILING NULLCOLS)

Table PHONE:
  182 Rows successfully loaded.
  11 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.


Space allocated for bind array:                  49536 bytes(64 rows)
Read   buffer bytes: 1048576

Total logical records skipped:          0
Total logical records read:           193
Total logical records rejected:        11
Total logical records discarded:        0

Run began on Sat Jun 20 23:34:27 2009
Run ended on Sat Jun 20 23:34:27 2009

Elapsed time was:     00:00:00.23
CPU time was:         00:00:00.04

 

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/13165828/viewspace-609548/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/13165828/viewspace-609548/